Quick Steps to Make Effective Data Visualisations


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.

  • Remove chart borders.
  • Show data in a useful order (alphabetically, or ranked, or some other important order).
  • Keep gridlines and reduce their colour, or remove them entirely.
  • If you remove gridlines, consider adding data labels.
  • If data labels have been added consider removing y-axis labels.
  • Add axis titles where the values shown aren’t inherently obvious.
  • Reduce the colour used in the chart.
  • Highlight the important data points.
  • Use white space effectively, but don’t leave excessive amounts.
  • Where possible use a chart title that helps the user see the points that you’ve highlighted and their context. Annotate any other points you’ve highlighted, why are they interesting?
  • Use colour to create a relationship between your annotations/title and the highlighted data points.

These steps will help you turn your default Excel charts into impactive and attractive data visualisations:


Data Visualisation Excel Examples How to

Nintendo Console Dashboard Update

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!

Dashboard With PicsWhere it’s appropriate I’ll definitely use this method elsewhere, it saves busting out VBA and causing a potential hiccup when a user doesn’t enable macros which is always a concern I have when using VBA.

The link to the blog post above is a tutorial for using this method if you’d like to find out how to do it yourself!

The file is available here if you want to prod around yourself.


Excel Examples

Game Dev: Balancing Adventurer Guild Simulator

For the past 2 weeks I’ve been working on developing a game in my spare time: it’s tentatively titled ‘Adventurer Guild Simulator’, as it’s about running an adventurer guild in a fantasy setting (think Dungeons and Dragons). In the game you’ll be hiring adventurers to go on quests to raise the reputation of the guild and to thwart the plans of villains within the area. Adventurers will be fighting monsters, solving the secrets of locations, gaining experience and collecting riches on your behalf. The game is being developed in Unity, which I can highly recommend to any budding game developers!

In an effort to research game development tips and tricks I’ve been devouring the Game Developer Conference (GDC) videos on Youtube. These are lectures and seminars held by leading game developers within the international game dev community, which are full of interesting anecdotes and lessons to be learnt from their past tribulations. One that left an impression was about balancing a game using statistics and Excel, right up my alley! It was run by Ian Schreiber, presenting the summary of his college-level course on game balance.

While I’ve been mainly using dummy figures for the game as of yet, I’ve taken my first steps into balancing some aspects of the game. This is a work-in-progress, so these figures won’t be set in stone, but for now here’s my attempts at balancing the levelling curve of adventurers within the game, using the average number of encounters per quest (mission) to drive how I want the game to be balanced. As the highest duration an adventurer can be temporarily hired for will be 6 days within the game, I wanted them to level up around once if they were hired with the max duration. With this in mind, I’ve tried to get to an average of around 5-6 missions per level up, where the adventurer is on a mission that is level appropriate (i.e. matches their level).

The experience points (XP) required to level were worked out as follows:


So, broken down it’s 300 as a base, plus 150 * (Adventurer’s level)x, rounded to the nearest multiple of 150. If the level is below 10 the exponent is increased by 0.05 (this was done to try and smooth the levels below 10 to closer to 5 missions per level up for x = 1.6 and x = 1.5).

The average number of missions required to level up was worked out with these formulae:

Average Number of Encounters per Mission:
= 35% (the chance of an enemy encounter) * 7 (the number of times during a mission an event occurs)

XP gained from a monster at X level:

So it’s (Monster Level)1.2 * 35 as a base rounded to the nearest multiple of 35.

Finally, average number of missions to level up:
=XP required for next level / (XP gain from a monster of level X * Average number of encounters per mission)

To make it easier to interpret, I used the following graphs as I moulded the formulae above to fit the requirements. The XP required per level that stays closest to 5 is a choice between XP required(Power 1.6) and (Power 1.5), and as Power 1.6 stays closer to 5 below level 10, I think I’m going to use that as my calculation for now!


Number of Encounters Per LevelXP Requirement Curves

Excel Examples Game Dev

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.

This was done using Excel – but I’m really interested in working on my Python coding and implementing this into a stand-alone embeddable tool.

If you want to have a poke around yourself and see how it all works, you can download the Dashboard here.


Excel Examples