This step-by-step guide will walk you through the process of creating a heat map in Excel with practical examples.
Microsoft Excel is designed to present data in tables. But in some cases, visuals are way easier to comprehend and digest. As you probably know, Excel has a number of inbuilt features to create graphs. Regrettably, a heat map is not on board. Luckily, there is a quick and simple way to create a heat map in Excel with conditional formatting.
A heat map (aka heatmap) is a visual interpretation of numeric data where different values are represented by different colors. Typically, warm-to-cool color schemes are employed, so data is represented in the form of hot and cold spots.
Compared to standard analytics reports, heatmaps make it a lot easier to visualize and analyze complex data. They are extensively used by scientists, analysts and marketers for preliminary analysis of data and discovering generic patterns.
Here are a few typical examples:
In Excel, a heat map is used to depict individual cells in different color-codes based on their values.
For example, from the heatmap below, you can spot the wettest (highlighted in green) and the driest (highlighted in red) regions and decades at a glance:
If you were thinking about coloring each cell depending on its value manually, give up that idea as that would be a needless waste of time. Firstly, it'd take a lot of effort to apply an appropriate color shade according to the value's rank. And secondly, you'd have to redo color-coding every time the values change. Excel conditional formatting effectively overcomes both hurdles.
To make a heat map in Excel, these are the steps to perform:
For this example, we've chosen Red - Yellow - Green color scale:
In the result, you will have the high values highlighted in red, middle in yellow, and low in green. The colors will adjust automatically when the cell values change.
Tip. For the conditional formatting rule to apply to new data automatically, you can convert your data range to a fully-functional Excel table.
When applying a preset color scale, it depicts the lowest, middle and highest values in the predefined colors (green, yellow and red in our case). All the remaining values get different shades of the three main colors.
In case you want to highlight all the cells lower/higher than a given number in a certain color irrespective of their values, then instead of using an inbuilt color scale construct your own one. Here's how to do this:
For this example, we've configured the following settings:
In this custom heatmap, all the temperatures below 45 °F are highlighted in the same shade of green and all the temperatures above 70 °F in the same shade of red:
The heat map you create in Excel is based on the actual cell values and deleting them would destroy the heat map. To hide the cell values without removing them from the sheet, use custom number formatting. Here are the detailed steps:
That's it! Now, your Excel heat map displays only the color-codes without numbers:
Another improvement you can make to your heatmap is perfectly square cells. Below is the fastest way to do this without any scripts or VBA codes:
For more information, please see How to align text in Excel.
Done! All the cells of your hat map are now square shaped:
Essentially, creating a heatmap in a pivot table is the same as in a normal data range - by using conditional formatting color scale. However, there is a caveat: when new data is added to the source table, the conditional formatting will not apply automatically to that data.
For example, we've added Lui's sales to the source table, refreshed the PivotTable, and see that Lui's numbers are still outside the heat map:
To force an Excel pivot table heat map to automatically include new entries, here are the steps to perform:
Now, your heat map is dynamic and will update automatically as you add new information in the back end. Just remember to refresh your PivotTable :)
If you don't want a heat map to be there all the time, you can hide and show it according to your needs. To create a dynamic heat map with a checkbox, these are the steps to follow:
In our case, the checkbox is linked to cell O2. When the checkbox is selected, the linked cell displays TRUE, otherwise - FALSE.
=IF($O$2=TRUE, MIN($B$3:$M$5), FALSE)
=IF($O$2=TRUE, AVERAGE($B$3:$M$5), FALSE)
=IF($O$2=TRUE, MAX($B$3:$M$5), FALSE)
Now, the heat map appears only when the checkbox is selected and is hidden the rest of the time.
Tip. To remove the TRUE / FALSE value from view, you can link the checkbox to some cell in an empty column, and then hide that column.
To hide numbers in a dynamic heat map, you need to create one more conditional formatting rule that applies a custom number format. Here's how:
=IF($O$2=TRUE, TRUE, FALSE)
Where O2 is your linked cell. The formula says to apply the rule only when the checkbox is checked (O2 is TRUE).
From now on, selecting the check box will display the heat map and hide numbers:
To switch between two different heatmap types (with and without numbers), you can insert three radio buttons. And then, configure 3 separate conditional formatting rules: 1 rule for the heat map with numbers, and 2 rules for the heat map without numbers. Or you can create a common color scale rule for both types by using the OR function (as done in our sample worksheet below).
In the result, you will get this nice dynamic heat map:
To better understand how this works, you are welcome to download our sample sheet. Hopefully, this will help you create your own amazing Excel heat map template.
I thank you for reading and hope to see you on our blog next week!
Heat map in Excel - examples (.xlsx file)
Table of contents