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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.