In my previous life as an accountant one of my main tasks was to analyse budget vs actual variances and then report these variances to department heads who were less than enthusiastic about finance, despite my repeated ‘Finance is Fun’ chants!
Let’s see how we can present variances in a chart that’s quick and easy to digest for anyone, even those who find finance a chore.
Charting Variances in Excel - Emoticon Rollercoaster Chart
In the spirit of ‘Finance is Fun’ we’re going to build what I call the Emoticon Rollercoaster Chart. It gets its name because the emoticons go up and down just like a rollercoaster…. sad, sad, happy, sad, sad, happy, happy, sad. …get it? 🙂
Don’t be fooled into thinking this chart is only for fun. There are some important lessons about data visualisation to learn here too, and if you don’t think the emoticons will go down well with your crowd then you can leave them out and make this into a ‘Finance is Serious Business’ chart.
Download the Excel File
Watch the Video to see how it’s done in Excel 2007/2010
Note: it's much easier to create this chart in Excel 2013. Click here to see how.
If you like this video and want me to do more please give it the thumbs up in YouTube so I know 🙂
Data Visualisation Lessons
- Remove clutter and noise from your charts – keep your charts clean and simple so the data can speak for itself.
- Anticipate questions and try to answer them by including that information in the chart, as I have done with the % variances included in the axis labels and the cumulative variance to date in a dynamic text label.
- Pitch it at the right level; if your report is for non-finance people then help them interpret it with clear legends e.g. % Variance good/(bad) is absolutely clear. There’s no point in using acronyms and symbols that only finance people understand. Instead of ‘good’ and ‘bad’ you could use % Variance +ve/-ve.
Answers to questions I know you’re going to ask – the answers are all NO!
Q1. Can I show the percentage and the emoticon in the same label?
The font for the labels is set to Wingdings so displaying the number and the emoticon in the same label will display the numbers in Wingdings too. Unfortunately unlike a cell, the label cannot take more than one font format.
Workaround: add another XY scatter series and insert the percentage labels in there.
Q2. Can I colour the % Variances in the axis labels red and green?
A2. The answer is also no.
You can’t colour individual items in the axis labels.
Custom Chart Labels Excel 2013
If you have Excel 2013 you can easily insert custom chart labels and have them automatically update colour changes using the new ‘Value From Cells’ option found in the Label Options menu.
Creating coloured custom labels in Excel 2013 is easy:
- Layout your data so that you have happy faces in one column and sad faces in another - see columns H and I below:
- Insert two new series (on the primary axis) using the Max data in column G > Select cells G3:G11 > Ctrl+C to copy > select the chart > CTRL+V to paste the series into the chart; one will be for your happy faces and the other for your sad faces,
- Then apply labels for each of these series using the (new in Excel 2013) Value from Cells option and linking to the cells containing your emoticons (H34:H11 and I4:I11):
- Colour the two series labels red/green accordingly.
- Hide the columns for these series since they’re only present to set the location of the emoticon above the highest point for each column.
Big thanks to Dave Hoffmeister for reminding about the XY Scatter Chart solution for Excel 2007/2010.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Your To-Do List
In no particular order:
- Download the workbook if you haven't already.
- Share this tutorial and the workbook with a friend or colleague.
- Have some fun with your reports, if you think your audience will appreciate it.
- Give my video the Thumbs Up in YouTube if you want me to record more often.
- Spread the 'Finance is Fun' motto if you work in Finance.
- Leave me a comment below and tell me what you do with your reports to improve audience engagement.