Create Heat Map in Excel

Use a heat map in Excel for rapid visualizations! We’ll show you various solutions to create heat maps and dynamic colored shapes. First, this tutorial will provide a detailed introduction the heat map optimization using Excel. Then, we will explain how to use advanced and straightforward solutions to improve the visual performance of your presentation.

You will learn what the heat map specifics are, how to set it for the most famous examples, and how to benefit from its features.

What is a heat map? How to create it?

A heat map helps us make a quick visualization comparison of the relations of the values in the data set. For example, in a bigger range (that contains the sales data for the past ten years in the monthly breakdown), we can immediately point out the periods that perform under or over the desired plan.

How can we do this using Excel?

Use maps to build great-looking dashboard templates using data visualization.

In this tutorial, you’ll learn how to:

  • Create heat maps using conditional formatting
  • Visualize resources using dynamic maps

Create a Heat Map Using Conditional Formatting

If you look at the picture below, you will get information about how the sales of different products related to each other throughout the years. How can we create a conditional formatting-based heat map in a few steps? We will show you this in the first chapter.

basic heat map

We don’t use Excel to do manual work and wouldn’t color the cells one by one. However, conditional formatting is an excellent tool to highlight the relevant values automatically.

Apply rules because if we manually manipulate the cells, we will have to raise problems! One of them is the time factor, for the process would take too long to complete.

The other is the disadvantage arising from the static depiction. In this case, changing the cells’ values would not come with the changes in the colors.

Essential criteria are that the colors must also vary when the values are changed in a given range. You can find the starting data table of the heat map in the illustration below.

How to create a gradient map using color scales?

Let’s see the step-by-step tutorial:

initial raw data set

1. Select the data set. In this example, it would be C3:H8

select range C3

2. Go to Home –> Conditional Formatting –> Color Scales. It shows various color combinations that can be used to highlight the data.

heat map conditional formatting rules

If we apply the green-yellow-red color scale, then the result will look like this:

result after color shading

It is worth examining closely how colors the cells by default. It ascribes green to the highest and red to the lowest value cells. And it used a color transition in between. If we take a look at the heat map, the color orange means the average. This would be fine, but there are different shades of orange, so which one is the true average?

It seems that this solution simplifies the task because the scale is relative. However, we must point out that this solution is “only” good for displaying the relationship between data set elements.

The high and low values are easily filtered out with the use of a color scale. However, to make the data visualization precise, we have to use a different method.

Use classification rules for group data

We wouldn’t like to see a gradient color scale; we would like to identify what values belong to what groups.

Use the following set of rules to highlight:

  1. Green the cells containing numbers larger than 750
  2. Yellow the cells between the values of 550 and 750
  3. Red the cells lower than 550

Let’s see how we can accomplish this: As first, Go to Home Tab. After that, choose Conditional Formatting. Finally, add a ‘New Rule’ using the ‘Format only cells that contain’ command. Then, in the dialog box, add the following rules:

distinct colors

You can see the result here:

distinct colors heat map

Explanation: What is the difference between the two heat maps?

The first method compares the numbers to each other and uses a gradient color scale based on that. In the second graph, we have given those three clear rules which grouped the values.

comparison

You can freely choose between them; we recommend you choose based on the given task.

Tip: How can we create a heat map where we only can see the colors? In this case, we do not need numerical values. Highlight all the cells that you would like to apply to this method too. Use the Ctrl+1 keyboard shortcut.

After this, a Format Cell dialogue box will open.

Navigate to the Number Tab and choose the Custom option. In the appearing field write “;;;” (3 semi-colons).

eliminate numbers from the range

Download the sample workbook (Compatible with Excel 2010 and above)

Additional resources

Free Excel Maps and Templates