Create a heat map table

Imagine you have some data showing performance of some teams in different categories:

results table

A very quick way of showing the results more visually is to create a thermal map.  You can do this very quickly using conditional formatting!  Download the sample excel sheet here.

  • First, highlight the values in the first column (result 1)
  • Then on the Home tab, click on the small arrow next to the Conditional Formatting button (in the Styles section)
  • Click on Color scales and then More Rules

Home tab

  • Ensure that “Format all cells based on their values is selected
  • Then in the Edit the Rule description section, choose a 2-color scale and choose 2 colors.  I’ve left the default colors in place for the first column (orange and light orange)

new rule

  • When you are done, press the OK button.  The column should automatically update

first column

  • You can then repeat this for each of the other columns, using different colors or the same color


  • Then all you have to do is format the remainder of the table:

draft 1

If you wish, you can also remove the numbers:

  • First highlight all of the numbers
  • Right click on the selection and choose format cells
  • In the number tab, click on custom
  • Then in the Type box, enter three semicolons ;;;
  • Press the OK button
  • The numbers will disappear

draft 2


What do you think?  You can download the sample excel sheet here.

Alesandra Blakeston

2 thoughts on “Create a heat map table

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s