How To Create A Heatmap In Excel 2016

It can often be difficult to get a measure of numbers in a spreadsheet, but Excel offers many data visualization techniques.

In this article I'll describe how you can apply heat map formatting to a column of numbers. We'll accomplish this with the Conditional Formatting feature available on Excel's Home menu.

Figure 1 shows the steps involved:

Click any single cell within the list.
Select Excel's Data menu.
Click the Sort Z-A command to sort the numbers from highest to lowest.
Select the numbers you wish to format.
Choose Conditional Formatting from Excel's Home menu.
Choose Color Scales from within the Conditional Formatting menu.
Choose a color scheme. Excel offers a live preview as you hover over each scheme, so you'll be able to see the impact before you commit to it.
The numbers are now formatted as a heat map.

heat map figure 1

Figure 1: The Color Scales Conditional Formatting feature makes heat maps easy to create.

As you can see, the Color Scales Conditional Format is easy to apply, but unfortunately is limited to cells that contain numbers. You cannot use Color Scales to format an entire row. You can manually craft conditional formatting rules that will format entire rows.

Do note that the first steps in Figure 1 involved sorting the data. This is an optional step, but results in a smoother look and feel to the heat map. Figure 2 illustrates two issues that can arise if you don't sort the data before applying Conditional Formatting.

In this scenario the data wasn't sorted before the Color Scale conditional formatting was applied:

The cells you wish to sort may already be preselected from when you applied conditional formatting, but if not select the range of numbers.
Select Excel's Data menu.
Choose the Sort Z-A command.
Choose Continue with the Current Selection in the Sort Warning dialog box. This is purposeful to illustrate a nuance in Excel.
Click Sort.
Most but not all the numbers are now in the wrong position. This error occurs when users unnecessarily select a portion of a list, as opposed to a single cell within the list.
Click the Undo command from Excel's Quick Access Toolbar or press Ctrl-Z to undo the sort and restore the original order.

heat mapping figure 2

Figure 2: Leaving a portion of a list selected while sorting can result in scrambling the data.

If you change your mind about applying conditional formatting, either click Undo immediately after you apply the formatting, or carry out the steps shown in Figure 3:

Select Excel's Home menu.
Choose the Conditional Formatting command.
Choose Clear Rules.
Choose Clear Rules from Entire Sheet.

heat mapping figure 3

Figure 3: You can remove conditional formatting from an entire worksheet or just from selected cells.

If you've applied Conditional Formatting to other areas of your worksheet you may instead want to select the cells that contain Conditional Formatting and instead choose Clear Rules from Selected Cells.