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 single steps? We will show you this in the first chapter.
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:
1. Select the data set. In this example, it would be C3:H8
2. Go to Home –> Conditional Formatting –> Color Scales. It shows various color combinations that can be used to highlight the data.
If we apply the green-yellow-red color scale, then the result will look like this:
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:
- Green the cells containing numbers larger than 750
- Yellow the cells between the values of 550 and 750
- 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:
You can see the result here:
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.
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).