How to create a Heatmap chart in Excel

Excel 2016
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 365

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 365

   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 365

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 365
  • Click OK.

Excel creates the heatmap:

The new Heatmap chart in Excel 365

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.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more