Microsoft Excel: Using Conditional Formatting To Make Heat Map

Microsoft Excel: Using Conditional Formatting To Make Heat Map

A data set’s comparative view can easily be represented visually with the aid of a heat map. For instance, the red cells in the dataset below shows the period when there were low sales.


Heat Map in Excel - Dataset


The colors are relative to the cells’ values. The green-colored cells have the highest values, followed by the yellow and the red has the least values.

Using conditional formatting to make a heat map

It is possible to make a heat map by highlighting the points in a dataset manually. The colors will however not change with a change in the cell’s value. If you want the color of the cells to change with a change in value, then you would have to use conditional formatting.

Imagine you have this set of data:


Heat Map in Excel - Dataset plain


To use the above data to make a heat set, follow these steps

  • Highlight the set of data

Heat Map in Excel - Select Data set


  • From the home tab, select conditional formatting and then color scales. There are many combinations of colors with which the colors can be highlighted. The initial color scale is however the most popular one where the big values have agreed shade and the smaller numbers have a red shade. It is possible to see a real time preview of the color scales by just moving the mouse over them.


Heat Map in Excel - color scale


You will get this result


Heat Map in Excel - result color scales


This however shows the values in a gradient. It is however possible to have just a shade of red, yellow and green to show the colors. To achieve this, you have to specify the set of values for a particular color, for instance, you could say below 200 is red, between 201 and 400 is yellow while above 400 is green. Based on this, you can use the more rules option under the color scales menu.


Heat Map in Excel - more options


You can then change the format style and value.


Heat Map in Excel - 3 color scale


Microsoft Excel: Making a heat map that is dynamic

Using the set of data below, it is possible for there to be a change in the heat map as the year is changed with the scroll bar.


Heat Maps in Excel - dynamic heat map data set


  • From the Developer tab, select control, then insert and then scrollbar. A scroll bar will be inserted in the worksheet once you click.


Heat Maps in Excel - Scroll Bar insert


  • Select format control after right-clicking the scroll bar.

  • Make the changes shown below in the dialog box for format control


Heat Map in Excel - Format Controls details


  • Select OK

  • The formula for B1 should read =INDEX(Sheet1!$B$1:$H$13,ROW(),Sheet1!$J$1+COLUMNS(Sheet2!$B$1:B1)-1)

  • Put the scroll bar under the set of data after resizing it.

  • It is also possible to use the radio button to make a heat map as shown below.


Heat Map in Excel - Radio Button


Making a Pivot Table Heat Map

Just like regular data, Pivot Data conditional formatting works in the same way. Using the data below: highlight cells containing the data.


Heat Map in Excel - Pivot Table Data

Select the color scale under conditional formatting in the Home tab.