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?
A1. No.
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.
Thanks
Big thanks to Dave Hoffmeister for reminding about the XY Scatter Chart solution for Excel 2007/2010.
Please Share
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.
David N says
You might not be able to get exactly the same smile and frown faces, but you can use the UNICHAR function in Excel 2013 with ampersand or CONCATENATE to get both the percentage and emoticon into the same label and without any need to use the Wingding font.
Example: =IF(F4>=0,B4&” “&UNICHAR(128534),””)
Catalin Bombea says
Hi David,
There are many way to get to the same result, thanks for this useful tip.
Catalin
Jae says
Is it possible to show the variance amount rather than happy faces? Great tutorial btw 😉
When I replaced the data with variance nos. and deleted the primary axis on the top, the chart formatting went crazy. It’d be really appreciated if you could advise on this.
Mynda Treacy says
Hi Jae,
Did you change the font in the labels? The font is set to Wingdings for the emoticons, but if you want to show numbers then you need to change the font.
Not sure why you deleted the primary axis. Perhaps you can post your question on our Excel Forum were you can share your file. Also let us know which version of Excel you’re using.
Mynda
jim says
yeah, tried that once and was told not to be so childish
some finance people certainly ain’t fun!
Tim says
Hi Mynda,
I struggled a bit with the Practice chart trying to duplicate the Completed chart with a minimum of looking back-and-forth to the on-line tutorial. After finally getting the Practice chart to work, I changed the data set to a table and was totally blown-away when the chart updated itself dynamically based on filters applied to the data.
Thanks so much for your clear presentations in your tutorials. I totally sold on using tables in Excel to present my data.
Best regards,
Tim
P.S. Being a scientist I am at a loss as to what cumulatives variances and such were all about, but I can certainly use the techniques to turn data into information. Now you only have to sell me on enjoying finance.
Mynda Treacy says
Hi Tim,
Great to hear you’ve mastered the chart.
It’s disappointing you’re not sold on FiF (Finance is Fun), I will have to think about how I can better market it 🙂
Mynda
Eve Peterson says
So clever:)
Mynda Treacy says
Thanks, Eve!
Julian says
I tried Excel 2007 many times and finally found the only way to have the wanted emoticons is to merge the two columns “% Variance L” and “% Variance J” into one column.
Mynda Treacy says
Hi Julian,
Did you try the XY Scatter chart method above? It worked for me in Excel 2007.
Mynda
Julian says
Hi Mynda,
I did follow your instructions on video step by step. It’s okay to get the “happy face” first, but when I applied the same approach later on for the “sad faces”, all the previous “happy faces” were gone. It’s really frustrated for me . Finally I tried my own way as I mentioned and It worked. Any way, thanks a lot for your great works. I enjoyed it very much.
Mynda Treacy says
Hi Julian,
It sounds like you didn’t insert two separate data series for the faces; one for the sad faces and one for the happy faces. Not to worry, the main thing is you got it to work.
Mynda
Julian says
Hi Mynda,
It’s very nice having talked to you. Expect to see more great posts from you.
Julian