How to create a Heatmap chart in Excel

Excel 365
A Heatmap or Heat Map chart looks like a table, which cells colors depend on the cell value. These charts are popular in biology, web and other analytics, geography, etc. Tabular data transformation for time series data projects one-dimensional data into two-dimensional time matrices, which simplify frequent pattern analysis.
A Heatmap chart in Excel 2016

Excel does not provide a chart template for heatmap charts yet. The simplest way to create a heatmap in Excel is to add Conditional formatting to the spreadsheet cells.

To create a heatmap, do the following:

   1.   Select the data.

For example:

The data for Heatmap chart in Excel 2016

   2.   On the Home tab, in the Styles group, click the arrow next to Conditional Formatting..., then select Color Scales and then click More Rules...:

Color Scales of Conditional Formatting in Excel 2016

Note: You can also choose the New Rules... from this drop-down list.

   3.   In the New Formatting Rule dialog box:

  • Under Select a Rule Type, be sure that the option Format all cells based on their values was selected,
  • Under Edit the Rule Description, on the Format Style dropdown list, select the 3-Color Scale option and then select the colors for Minimum, Midpoint, and Maximum values:
New Formatting Rule dialog box in Excel 2016
  • Click OK.

Excel creates the heatmap:

The new Heatmap chart in Excel 2016

Note: See also how to hide the cell values in Excel without hiding cells.

Heatmap charts are also handy for categorical data visualization if there is a natural ordering for the categories when you need to emphasize data relationships.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.