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. This tutorial will provide a detailed introduction of the heat map optimization using Excel. We will explain how to use simple and advanced solutions in order to improve the visual performance of your presentation.

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

What is a heat map? How to create it?

A heat map helps us to 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 10 years in the monthly breakdown) we immediately can point out the time periods perform under or over the desired plan.

How can we do this using Excel? This is what today’s article is all about! 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 take a 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 single 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. Conditional formatting is an excellent tool to automatically highlight the relevant values.

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 the changing of the values of the cells would not come with the changes in the colors.

Very important criteria are that when the values are changed in a given rang, then the colors must change also. You can find the starting data table of the heat map on 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 end result will look like this:

result after color shading

It is worth to examine 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. We have to point out again that this solution is “only” good for displaying the relationship between the elements of a larger data set.

The high and low values easily filtered out with the use of a color scale. 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, chose Conditional Formatting. Finally, add a ‘New Rule’ using ‘Format only cells that contain’ command. In the dialog box, add the following rules:

distinct colors

You can see the end 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 make your choice based on the given task.

Tip: How can we create a heat map where we only can see the colors? In this case, we have no need for 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