What is a Heat Map?

A heat map is a two-dimensional graphical representation of data, where values are represented by a color scale in a matrix layout. Color-coded cells allow easily identifying the outliers and see the patterns in your data.

Excel’s cell-based matrix system already gives you a pretty good place to start as you can simply adjust cell dimensions to match the layout of a heat map. As for highlighting the cell matrix, you’re going to want to use Conditional Formatting to make the table update automatically when data changes. In this article, we’re going to show how you can create a dynamic Excel heat map with Conditional Formatting for better presentation.

Creating a Heat Map in Excel with Conditional Formatting

Let’s see how this works on an example. Assume that you have a data table like shown in the screenshot. Feel free to download this workbook below.

Download Workbook

To create an Excel heat map, begin by selecting the set of values to be used in the visualization.

Now, go to HOME > Conditional Formatting > Color Scales. Here, you will see highlighting options to choose from and you need to choose from high to low, or low to high layouts. For example, the first option shown below means Red highlight for low values and Green for high values.

Click on one of the options to apply that color layout. The highlight colors will be applied based on cell values and will readjust automatically when values change.

Colors and highlight rules can be modified from HOME > Conditional Formatting > Manage Rules. Here, go to the Conditional Formatting Rules Manager dialog to make changes.

Click Edit Rule or double-click the rule line to open the Edit Formatting Rule dialog. You can modify the rule and the colors in this dialog. For example, you can decide between 3-color and 2-color scales, or manually enter the minimum and maximum values.

When you’re done with your settings, click OK to apply changes. At this point, we have a basic Excel heat map that highlights the table cells based on their values, resulting in a better representation of data for identifying outliers. Let’s now see how we can further improve this heat map.

Hiding the Numbers

Some heat maps do not contain any numbers at all. However, the heat map we’ve created uses the actual cell values and deleting them will affect our heat map. You can do the following to hide the cell values without deleting them:

Select the data range
Press Ctrl + 1 to open the Format Cells dialog
Go to the Number tab
Select Custom in the Category list box
Type in 3 semicolons (;;;) into the Type box
Click OK to apply the number formatting and hide the values

Editing the formatting this way will hide the values while keeping the highlight colors. The values are still there, but Excel is not displaying them. For more information about this method see how to hide formulas in Excel using the Number Formatting feature.

Square Cells

Another improvement we can make is making the Excel heat map cells square shaped. Technically, you can do this by manually changing the cell height and widths, but the column headers might get cut off when you do this. We recommend using vertical alignment to avoid this from happening and also resizing cells much faster.