Excel Heat Maps

Excel Heat maps can speed up interpretation of data and focus the reader’s attention to problem or key areas. And we can create them easily using Conditional Formatting. To be fair it’s less of a map and more a heat table, but nonetheless they’re referred to as heat maps.

Let’s take this example of student test data. Presented as a table of numbers makes hard work for the teacher to quickly identify and focus on students who are below average.

Whereas if I apply some Conditional Formatting I can instantly focus on the below average students as their test scores are in shades of red.

And if I sort the table in descending (or ascending) order on the Average column it becomes even easier.

I can also add a Sparkline to show the trend of a student’s performance, which makes it quicker to see whether the trend of test scores is positive or negative.

So you can see with just three techniques; Conditional Formatting, Sorting and Sparklines, we’ve taken a sea of numbers and made the instantly understandable.

Download the file

Excel Heat Maps with Conditional Formatting

To apply the Conditional Formatting simply select all the cells containing the test scores > Home tab > Conditional Formatting > Color Scales:

Tip: Hover over the icons to see a preview of the color scales applied to your data.

I’ve used the very first color scale icon, but you can set your own colors by clicking ‘More Rules’. This opens the Conditional Formatting dialog box below where you can choose from a 2 or 3 color scale:

The default is to set the scale based on the lowest and highest values (see Type drop down in the image below), but you can change this to be a specific value, number, percentage, percentile or even based on a formula:

Modifying Conditional Format Rules

If you apply a color scale and then change your mind, you can edit the rule by opening the Conditional Formatting Manager: Home tab > Conditional Formatting > Manage Rules:

Select the rule from the list (I only have one), then click the ‘Edit Rule’ button at the top. Alternatively, you can delete the rule and start over.

Hiding Values in Excel Heat Maps

In the heat map below I have the 2016 daily solar exposure (which is the total solar energy for a day falling on a horizontal surface) for where I live:

The heat map is effective in helping us to see patterns and trends, and even anomalies.

We could use a Custom Number format (;;;) to hide the values and allow the color scale to speak for itself like this:

To apply the Custom Number format select cells B6:M36 > CTRL+1 to open the Format Cells dialog box > on the Number tab select the Custom category > in the ‘Type’ field enter three semi-colons as shown in the dialog box below:

More Custom Number format examples: Custom Cell Formats

Choosing Colours

Your choice of color should consider the message and the data. For example, we instinctively associate red with bad and green with good. And in the daily solar exposure heat map above I used blue and red, as those colours are commonly associated with cold and hot.

Another consideration is for those with color vision deficiency, or color blindness. This post talks about the use of color in charts and has some resources for choosing colors, including colors for color vision deficiency.

More Conditional Formatting

Using Conditional Formatting to create a heat map is just one of many uses and features available. If you haven’t used Conditional Formatting before then read that post.

And once you’ve mastered the standard Conditional Formats available you can take it a step further and use formulas to set your conditional formats.