How to create improved data visualisations in Excel

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood CPA:
Welcome to Excel Tips. My name is Neale Blackwood, and in this episode we're going to look at some data visualisation ideas for Excel. Now in Excel, data visualisation includes charts, sparklines, which are basically little charts, as well as conditional formats, formulas, and even emojis. Yes, emojis have arrived in Excel. What I'd like to discuss is how you can use different types of charts to convey the information in a better way that is easier for people to understand and also includes data that they actually want rather than have to figure out by looking at a chart. Data visualisation in Excel allows you to pick the correct chart to get whatever point you're trying to make across. And so different charts work with different data sets and data structures. The example I'm using for this episode is comparing one month's data from this financial year with the corresponding month from the previous financial year. So what we want to analyse is the movement in the customer categories between these two periods. So that's what the chart is going to focus on. There's a companion video for this episode as well, which goes into a lot of the instructions in that to create the charts. And all of the formulas are already created, so the structures are already in the examples. You can also download the example files from the INTHEBLACK website. So you can check out the website to get that and to see the video. So basically we want to compare one month from this financial year to the corresponding month from the previous financial year.

Column charts:

The common way to display this is to use a column chart or a clustered column chart, is the other name that Excel uses. And basically you've got one column for the last financial year's month and then another column for this financial year's month. They're presented next to each other, and you can see whether the values have gone up, gone down, or whether they're flat. Which is okay, that just gives you an indication of the direction. It doesn't tell you what the difference is. In many cases, that's what's important. So the difference between what this month was compared to this month last year. So that's the variance. Oh, and by the way, the techniques that I'm explaining apply to any two metrics. So what we're looking at is actuals from one financial year to the other, but you could be comparing actuals to budget, actuals to forecast, total for this year versus total for last year. So any two metrics that you want to compare, you can use these techniques to cover it.

Variance charts:

Whilst a column chart is good, just to show sort of the direction, it's not great to show the value. So a better way to show it is something called a variance chart. So the variance chart actually plots the difference. So you see a positive variance as a column above the zero, and then a negative variance as a column below the zero. And this really then highlights the important part of what we're looking at. We're looking at the differences between the two periods. And the advantage with that is you can also include the total in that. When you are plotting just the values, it's really hard to include the total because the total will really throw out the vertical axis and you won't see because the total will take over the chart if you like. Whereas in a variance chart, the total is usually included because it's usually okay when you're plotting the variance.

Recommended charts:

Now, something that's useful in Excel when you are creating charts is what's called the recommended charts. So when you click on the insert ribbon, in the middle is the charts section, and they now have a nice big button that has the recommended charts, so it's a good idea to check that out. So just select the range that you want to plot, click on recommended charts, and it'll give you some ideas. If you select those two metrics, then it is the column chart that's recommended. If you select the variances and also click the recommended chart, you'll also see that the recommended chart shows what you're after in terms of it's giving you a single column for each category. In this case, we're looking at customer category, and we're seeing the different customer categories between the periods.

Cleaning up charts:

Okay, a few ideas when you are creating the various charts. Think about what you can remove from the chart whilst not losing any information. One of the first things I tend to do is remove the grid lines. Grid lines sort of clutter the chart in that there are a line on there that you might think you need it, but when you take it out, you'll find that the chart is just as readable. And you've got to remember when you're looking at a column chart, you are not really trying to figure out what the exact value of that column is or the comparison. Basically just removing the grid lines cleans the chart up. It looks a lot cleaner and you don't really lose a lot. And if you do need the exact figure, then one of the techniques that I demonstrated in the video is you can just add data labels. So the variance chart is really good. It shows you the variances in the dollar terms, but you can also think about percentage terms. So what was the percentage movement? These aren't mutually exclusive. You can actually create a chart for the dollar movement and then a chart for the percentage movement and have them side by side.

Jacqueline Blondell: If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Neale Blackwood CPA:

Moving Labels:

By the way, there is a technique demonstrated in the video that shows you how to move the labels on the horizontal axis below the chart. One of the things you'll find when you are plotting positives and negatives is that the category names below the axis will get covered. And so there is a technique that you can move the axis labels below the chart, which makes the chart a lot more readable. With the percentage movement, the chart that I created actually removed the vertical axis, so we put the percentage movement either above or below the columns. It's a very clean chart. It's really easy to understand, and it's really easy to pick up what changes have been made and how they've impacted the total change.

Sparkline charts:

Now, if space is at a premium, one of the charts that is useful to employ is what's called a sparkline chart. Now, sparklines were invented by a fellow named Edward Tufte, who's written quite a bit on data visualisation. And a sparkline is a chart that is inside a cell, and so it's very small. And in the companion video, I created a sparkline that mimics the dollar value variance chart. One of the things that's missing in the sparkline is that there's no labels on there. And so in the companion video, I shared a technique where you can use some Excel functions to actually create labels for a sparkline. You can check out the video to see that.

Waterfall (bridge) charts:

When you are comparing two separate metrics, one of the most commonly used data visualisations is what's called a waterfall chart. I prefer the term bridge chart, but the waterfall chart is the common name, and it basically has a column for the starting position, which is in this case, let's say October 22. And then it shows the movement in the various categories, and then you arrive at October 23. So you can see the size of the movements, and then you can get some sort of perspective because you're also looking at the total of the, in this case, sales. The reason I prefer the terminology of a bridge chart is that waterfalls only fall down, whereas when you are comparing costs and sales and things like that, there's ups and downs. And so I think a bridge chart is a better description of it, but as I mentioned, waterfall is the common description of it.

Conditional format - data bar:

So the types of data visualisations you use for categories where you don't have a lot of different categories is different to if you need to analyse a lot of data. So let's say we wanted to look at all of our customers. In that case, what we can do then instead of employing charts is we can use a conditional format. So again, the variance is what we want to focus on, so we can have the two Octobers listed side by side and then a variance. And in that variance column, we can insert a conditional format known as a data bar. And data bars allow you to have... It's like a bar chart within the cell, and it can show positive and negative movement. Now, the beauty of it is, is the size of the bar within the cell is in comparison to the rest of the data. So basically, if you've got a data bar that goes right across the cell, then that is the largest. And if you've got a negative movement, if it goes right across the cell to the left, then that's the largest negative movement. And the data bar in the video has a blue for the positive movement and a red for the negative movement.

Making charts for accessibility:

Speaking of colours, I should mention colorblindness. About one in 12 men has some form of colorblindness, and red-green is the most common. So just be careful when you are creating your colours that you are using for the data visualisations that you take that into account. It doesn't affect women as much. It's only about one in a hundred women have any form of colorblindness.

Conditional format – icons:

Another type of conditional format that's useful is an icon. So you can insert icons into cells, and the example I've used in the video is to use the up and down arrows, but to only show them for variances that are greater than 25%. That allows you to use exception reporting where you only are seeing icons if something is worth reporting, if you like. The example I used was 25%. So anything more than 25% increase or less than a 25% decrease has an icon next to it, and it's an upward-facing green arrow for a good variance and a downward-facing red arrow for a bad variance. And that's an example where using an icon is good because just using colour, as I mentioned, can be an issue for people who are colorblind.

Emojis:

And emojis. So if the icons that are available in the conditional formatting aren't enough, you might want to consider using some emojis. The keyboard shortcut for emojis is the Windows key. So hold the Windows key down and press the full stop, and that allows you to insert emojis into a cell or into a formula. If you are using them in a formula, you need to enclose them in quotation marks. And the example in the video shows a thumbs up and a thumbs down emoji. I did change it in the video because I did have red and green as the colours, and I changed that to a blue for the thumbs up, and I left the thumbs down as a... It's a sort of an orangey colour, I guess. Okay, so there are lots of options for data visualisations in Excel. I've just scratched the surface on some of them here. But between charts, conditional formats, formulas and emojis and icons, you can usually find something that helps you get across what you are trying to communicate using visualisation rather than just a report. Thanks for listening. Hope you found that useful.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.