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.

Create an Excel Heat Map

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.

Create an Excel Heat Map

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

Create an Excel Heat Map

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.

Create an Excel Heat Map

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.

Create an Excel Heat Map

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.

Create an Excel Heat Map

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.

Create an Excel Heat Map

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.

Create an Excel 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:

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

Create an Excel Heat Map

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.

Create an Excel Heat Map

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.

  1. Select the title cells
  2. Press Ctrl + 1 to open the Format Cells dialog
  3. Go to the Alignment tab
  4. Click on the diamond in the Orientation input to adjust alignment to top
  5. Click OK to apply the orientation

Create an Excel Heat Map

Equalize the height and widths of cells to display cells as squares.

Create an Excel Heat Map

For other Conditional Formatting features see here.