How to make a heat map in Excel with or without numbers

What is heat map in Excel?

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:

• Air temperature heat map - is used to visualize air temperature data in a certain region.
• Geographical heat map - displays some numeric data over a geographic area using different shades.
• Risk management heat map - shows different risks and their impacts in a visual and concise way.

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:

How to create a heat map in Excel

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, we will be using conditional formatting color scale. Here are the steps to perform:

1. Select your dataset. In our case, it's B3:M5.
   

2. On the Home tab, in the Styles group, click Conditional Formatting > Color Scales, and then click the color scale you want. As you hover the mouse over a particular color scale, Excel will show you the live preview directly in your data set.

   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.
   

Make a heatmap with a custom color scale

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:

1. On the Home tab, in the Styles group, click Conditional Formatting > Color Scales > More Rules.
   

2. In the New Formatting Rule dialog box, do the following:
   • Pick 3-Color scale from the Format Style drop down list.
   • For Minimum and/or Maximum value, choose Number in the Type drop down, and enter the desired values in the corresponding boxes.
   • For Midpoint, you can set either Number or Percentile (normally, 50%).
   • Assign a color to each of the three values.

   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:
   

Create a heat map in Excel without numbers

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:

1. Select the heat map.
2. Press Ctrl + 1 to open the Format Cells dialog.
3. On the Number tab, under Category, select Custom.
4. In the Type box, type 3 semicolons (;;;).
5. Click OK to apply the custom number format.
   

That's it! Now, your Excel heat map displays only the color-codes without numbers:

Excel heat map with square cells

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:

1. Align column headers vertically. To prevent column headers from getting cut off, change their alignment to vertical. This can be done with the help of the Orientation button on the Home tab, in the Alignment group:
   

   For more information, please see How to align text in Excel.

2. Set column width. Select all the columns and drag any column header's edge to make it wider or narrower. As you do this, a tooltip will appear showing an exact pixel count - remember this number.
   

3. Set row height. Select all the rows and drag any row header's edge to the same pixel value as columns (26 pixels in our case).
   

   Done! All the cells of your hat map are now square shaped:
   

How to make a heat map in Excel PivotTable

Essentially, creating a heatmap in a pivot table is the same as in a normal data range - by using a 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:

How to create a dynamic heat map in Excel with checkbox

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:

1. Insert a checkbox. Next to your dataset, insert a checkbox (Form control). For this, click the Developer tab > Insert > Form Controls > Checkbox. Here are the detailed steps to add a checkbox in Excel.
2. Link the checkbox to a cell. To link a checkbox to a certain cell, right click the checkbox, click Format Control, switch to the Control tab, enter a cell address into the Cell link box, and click OK.

   In our case, the checkbox is linked to cell O2. When the checkbox is selected, the linked cell displays TRUE, otherwise - FALSE.
   

3. Set up conditional formatting. Select the dataset, click Conditional Formatting > Color Scales > More Rules, and configure a custom color scale in this way:
   • In the Format Style drop-down list, select 3-Color Scale.
   • Under Minimum, Midpoint and Maximum, select Formula from the Type drop-down list.
   • In the Value boxes, enter the following formulas:

     For Minimum:

     =IF($O$2=TRUE, MIN($B$3:$M$5), FALSE)

     For Midpoint:

     =IF($O$2=TRUE, AVERAGE($B$3:$M$5), FALSE)

     For Maximum:

     =IF($O$2=TRUE, MAX($B$3:$M$5), FALSE)

     These formulas use the MIN, AVERAGE and MAX functions to work out the lowest, middle and highest values in the dataset (B3:M5) when the linked cell (O2) is TRUE, i.e. when the checkbox is selected.

   • In the Color drop-down boxes, choose the desired colors.
   • Click the OK button.
     

   Now, the heat map appears only when the checkbox is selected and is hidden the rest of the time.
   

How to make a dynamic heat map in Excel without numbers

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:

1. Create a dynamic heat map as explained in the above example.
2. Select your data set.
3. On the Home tab, in the Styles group, click New Rule > Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter this formula:

   =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).
   

5. Click the Format… button.
6. In the Format Cells dialog box, switch to the Number tab, select Custom in the Category list, type 3 semicolons (;;;) in the Type box, and click OK.
   

7. Click OK to close the New Formatting Rule dialog box.

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.

Practice workbook for download

Heat map in Excel - examples (.xlsx file)