Create a Heatmap in Excel

Last week, I showed you how to use Excel’s Conditional Formatting menu to add cell formats to highlight specific data values. Here, I’ll show you how to easily use the Color Scales options in that menu to create a Heatmap.

Simply put, a heatmap is a table where the data are visualized using color. They pop up fairly regularly these days, sometimes showing the actual data values and sometimes not, like these two I pulled from FlowingData.

I’ll use the OECD Better Life Index data again to create this heatmap. First, select a column of data, navigate to the Conditional Formatting menu on the Home tab, select Color Scales and More Rules…

Here, you are able to choose the specific color palette you want to use, selecting the color for the lowest and highest values. The default color range is orange for the Lowest Value and a yellow for the Highest Value. I’m not sure why the default is opposite the way most of us would use color to encode data (with darker colors encoding higher values), so you’re going to have to switch those around. (Also note that the Mac and PC versions of this menu do look fairly different, but they work in the same way.)

I’ve completed the first 5 columns here, each with a different color scale. Be sure to select each column individually. If you apply the format to a group of columns, they will use the same color ramp and Excel will assume they are drawn from the same distribution.

Hide the Numbers

This is a decent heatmap and you can see some of the patterns emerging. There are times, however, when you might like to create a heatmap without seeing the actual data values, to create something like this:

When I ask people how they would hide the numbers and keep the colors, I typically hear a couple of guesses: Either delete the numbers or turn them to white. Though easy, neither work: In the former, the text and the colors both disappear—they are linked to the data, remember? In the latter, you can still see them through the color scales, so they’re not hidden, just a different color.

So what to do? Well, we can use a little number formatting trick to hide the data values while maintaining the conditional formatting. Select the entire heatmap and then right-click to select Format Cells. (An easier way is to use the CTRL+1 (PC) or CMD+1 (Mac) keyboard shortcut, which, by the way, is my favorite Excel keyboard shortcut of all time and works for every object in Excel including parts of your charts such as axes, backgrounds, and lines.)

At the bottom of the Format Cells menu is the Custom option. I won’t go into how Excel does its custom number formatting here (Excel Tactics has a terrific post on Excel number formats), but there are basically four slots, each separated by a semicolon: <Positive Numbers>;<Negative Numbers>;<Zeros>;<Text>.

In our example, type in three semicolons (“;;;”) into the Type: box.

Click Ok and your numbers will be hidden, but are still there and available for you to use or modify, and the colors remain. Neat little trick, eh?