Garreth Hanley:
This is Excel Tips podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood:
Using heat maps for sales and cost information
Welcome to the Excel Tips podcast. My name is Neil Blackwood, and in this episode we're going to look at heat maps. Now heat maps are typically used to display climate or weather information. But have you thought about using them to analyse your sales information or your cost information? Well, you can in Excel and we'll cover how you can do that in this podcast as well as looking at a few other options that you've got analysing top and bottom values.
So a heat map is a data visualisation that uses colour to identify high numbers and low number and typically a single colour is used, but you can use multiple colours. That does tend to get a bit gaudy, so a single colour is advised and so the low numbers in terms of sales could be white and then the high numbers could be a dark blue and then there's shades of blue used to identify all of the numbers in, so this allows you to look at a large report.
Now in our example, and I recommend you check out the video cause that obviously here for data visualisations seeing things makes a big difference.
Also, the video has all of the instructions to create these heat maps in Excel. They're actually called colour scales in Excel, but they do create the heat maps. So, in the video you'll see that we've got a two-dimensional grid that we've used to plot every sales day for the 23-24 financial year and so you can see all of the sales days in one single page.
Colour scales options and settings
Now, as I mentioned, they're called colour scales in Excel and they're part of the conditional formatting option, which is on the home ribbon. When you click on colour scales, you'll find that there's about 12 icons displayed, but that's a little bit misleading because there's really only four options. There's two options, and then there's two options within each of those two options. So the first set of options is whether high is good or whether low is good, so if you're looking at sales then obviously high is good, if you're looking at something like costs or your golf score, then low is good. And so based on being good, you'll use a colour. Now the standard colours that are used are green, is good and red is bad, I'll come back to that when we when we talk about colour blindness.
So that's the standard colouring that are the default colours for conditional format.
Now the other thing you can change is how many colours you use. You can use either two or three colours. When I say that it can be a bit misleading because you can use white as a colour and when you use white as one of the colours you are in effect just using one colour and actually, that's what I recommend. If you use two colours or especially 3 colours, it does get very gaudy and it is actually harder to read just using white and a colour allows you to create a visualisation that's a bit easier on the eye than using two or three colours.
Now colours, colour blindness is something you should take into account. Colour blindness effects. About one in 12 men and about one in a hundred women. So it is a male problem, but it is something that you should consider when choosing the colours. Red-green colour blindness is one of the most common forms.
Now colours that are safe for people with colour blindness are colours like blue, orange and grey, and you'll find that Excel’s default charts when they create visualisations tend to use those colours first. So when you see the 12 different icons on the colour scale options, it's basically just different combinations of those four different types.
Now in the grid that was created to show the complete year, there were some what I'll call illegal days. So these are things like the 31st of September, so the grid had 31 days across the top and the months listed down, so obviously the 31st of the of September doesn't exist. So instead of showing that as white, which sort of looks like a low sales day when it's actually a no sales day, I've changed that to use a separate conditional format to turn any illegal days black, so they're they're sort of ignored. You can have as many conditional formats as you need in Excel.
Conditional formatting and top and bottom rules
One of the problems with heat maps is it can be a little bit overwhelming to try and understand all of the different shades of the one colour. It can also be a little bit hard to pick up patterns, so consider using a top and bottom rules conditional format. What this does is it allows you to isolate the top certain number of items in isolation as well as the bottom number of certain items in isolation.
So, this can make it a little bit easier maybe to pick up patterns on when these high values are appearing.
Excel has a conditional format called top and bottom rules and within that there are options for a top ten and a bottom 10. Now it's not as restrictive as it sounds, so the top and bottom numbers are available for you to change. In the case of the video, we looked up the top 12 sales as well as the bottom 12 sales. And you can apply a colour to those. Now this colour is the solid colour, it’s not a colour that is shades of a different colour, so that can be a lot easier to spot patterns if you like, on where the high sales days are and also the low sales days.
A problem with the top ten bottom ten is you would like the ability to change that number and just see the effect on the data visualisation. Unfortunately the top ten bottom 10 doesn't allow interaction with the ship. Many conditional formats do, but not the top ten bottom 10. So what I've done in the example is to show you how you can use formulas in helper cells to actually work with conditional formats to allow that flexibility in the top ten bottom 10 so you can change a cell. Value and that will automatically update the data visualisation.
Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show in the black. Search for in the black on your favourite podcast app today.
Neale Blackwood:
So the functions that work well with the top ten bottom 10 or whatever number you choose are the large function and the small function. Now they're a little bit similar to the MAX and the MIN function. So Max finds the largest value. Whereas the large function actually allows you to define the position number that you want to identify. So if we use 12, it will find the 12th largest. Number and then we can use that as part of a conditional format to actually identify those, and the small function that allows you to find the value of in this. Case the 12th. Smallest number in that range and so it's very easy to use the cells value conditional format.
To link to individual cells so we can specify a colour for the top 12 and then a different colour for the bottom 12. And we also need to cover those illegal dates as well, which again are used black for. So heat maps and those top and bottom rules can make understanding large volumes of numbers a lot easier. It can help you to find trends or patterns can be really useful for sales or any other values that are affected by seasonality or special dates like say Christmas or Easter. So check out the video and see if the heat maps and the top and bottom rules can help you understand your numbers a. Little bit better. Thanks. Listening.
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 in this episode. And we hope you can join us next time for another episode of Excel Tips.
