If you have data presented in a table structure and you want to make a comparison of the figures in the table, it can be difficult to understand quickly. You may end up spending more time than expected on just a simple task. This is where a Heat Map in Excel comes to the rescue.
What is a Heat Map in Excel?
Heat Map in Excel is a way of visually presenting data using colour shades in the cells to compare the values to make it easily understandable for the user. It can be applied on a single column or multiple columns with numeric values only.
When you have values to compare, it is always easier to understand when it is visually presented. Just like when you analyse data and present your data using charts, graphs or maps. However, there are some data that you need to present in a table format.
So let’s consider the above data.
This is a sales report having the total number of sales of product for each month according to the different regions.
To create a Heat Map in excel using this data, you should follow the steps below:
- Click any cell within the list
2. Go to ‘Data’ menu
3. Select ‘Sort Z – A’ to sort the numbers from the highest to the lowest
4. Select /Highlight the data range of only the cells that have values
5. Click on ‘Home’ tab – > ‘Conditional formatting’
6. Select ‘Colour Scales’ and choose your preferred colour scheme. Excel will show a live preview of what each selection looks like.
Once you select an option, a colour shade will be assigned to each cell according to their values.
You will now have a heat map formatted table like this one below:
If you take a good look at the heat map above, you will see that you can quickly identify and compare the values in the cells. You can easily see that the cells with highest values are coloured in green, while the lowest values are coloured in red.
Also, you can easily compare the sales from all four zones and quickly tell that all four zones had less variations in the number of sales in the months of August and October, while other months witnessed higher variations in number of sales.
And if you want to hide the values and display only the colours, you can use the “Custom Formatting” function by following the steps below:
1. Select the heat map data
2. Press Ctrl + 1 to go to format cell or use keyboard shortcut Ctrl + Shift + “ to take you directly to the
Custom option
3. Select ‘Custom’ option and type ‘ ;;; ’ in the box field or you can use keyboard shortcut Ctrl + Shift + “
to go directly to custom dialog.
Click ‘Ok’
And this is what you will get.
You can now see that all the numbers are no longer visible in the cells. They are now hidden.
Unfortunately, heat maps can only be used on cells or columns that contain numeric values.