This is an example of the default settings for a bar chart in Excel. This is functional but could be far more effective; here are my main steps I use to create quick, effective data visualisations.
Advertisements
Tag: Data Visualisation
Analytics and Ethics
Upon the enlightening release of the Cambridge Analytica exposé that revealed the company’s underhanded tactics it utilises to influence and manipulate people with a combination of data analytics, espionage, and ‘honeypotting’, I think it’s incredibly important to discuss how the analytical community needs to ensure a commitment to honest and unbiased analytics, and ethically sourced data.
HOW TO… Excel: Create a Dynamic Data Range for a Chart
Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.
Nintendo Console Dashboard – Linked Pictures
I was taking a glance around the Excel tag in the WordPress Reader, when I came across the following blog post about Linked Pictures in Excel.
This was a little bit of a revolution to me; when I made the Nintendo console dashboard I struggled to work out a passable fix for including dynamic pictures in VBA, ultimately deciding to scrap the code and just go with no pictures. Now that I know how to use Linked Pictures with named ranges to allow dynamic updates I could easily go back and update the old Dashboard!
Nintendo Console Dashboard
If it weren’t apparent enough from my Splatoon 2 analyses, I love Nintendo. So, of all the things one can make a Dashboard about, of course I went ahead and created a Dashboard using data about Nintendo’s array of home consoles.
HOW TO… Excel: A Truly Non-Volatile Solution for Dynamic Data Tables
This is a follow-up to the last tutorial. Thanks to reddit user /u/small_trunks for noticing my blunder!
HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table
This is a continuation of the previous tutorial.
Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.
HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes
Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch of a button. I have a few pet peeves with Pivots however, mainly being that they require data be in a particular layout; that they can be restrictive on the type and layout of the output they produce (although they do provide a good level of options); and when updating or changing data within a spreadsheet Pivoted data does not refresh unless it’s explicitly told to (which can create a jarring experience when handling and presenting larger data sets).
Splatfest Analysis (Part 3)
In this final Splatfest Analysis, we will be taking a closer look at the distributions of the 9 games I had with the Splattershot to determine whether its consistent place in the top 3 was due to me consistently playing well with the weapon, or whether the averages were skewed by particularly high leverage outliers.
Zipf’s Law
After watching a long session of ‘VSauce’ videos (great brain food videos, albeit very addictive!), I came across this video discussing ‘Zipf’s Law’. Zipf’s law states that in any corpus of natural language, the frequency of any word is inversely proportional to its rank in the frequency table. This Zipfian distribution applies to many different types of data studied across a variety of fields (the video discusses a large variety of these instances). Zipfian distributions also follow the ‘Pareto Principle’, the 80-20 rule. 80% of the words used in any corpus are only 20% of the unique words used.
After being utterly captivated by this phenomenon, I decided it would be fun to check whether my blog, being a corpus of natural language, followed Zipf’s law.
