Excel Heat Map (Table of Contents)
Heat Map in Excel
Heat Map in Excel is used to show data in different color pattern. This helps in judging whether the data target is achieved or not. In Heat Map, data is shown in a different color pattern so that we can see which data point is below the limit and which one is above the limit. We can use two or more than two colors to see the data pattern.
How to Create a Heat Map in Excel?
Instead of manual work, conditional formatting can be used to highlight value – based cells. If you change the values in the cells, the color or format of the cell will automatically update the heat map in conditional formatting based on the predefined rules.
Let’s understand how to Create a Heat Map in Excel by using some examples.
Example #1
Here we got the sales data of some part numbers. Where we have the sales numbers in Column G and H for two months. Now we want to apply Heat Map in data shown below.
For that, first, select both the columns G and H. Now go to Home menu and select Conditional Formatting as shown below.
Once we do that, we will get a drop-down menu list from there as shown below. Now from Color Scales, select any color pattern. Here, we have selected the second option which is Red-Yellow-Green color pattern as shown below.
Once we do that, we will see the heat pattern to all selected color scale as shown below. This shows that the value which is less than 50 is in green color shades. The mid value which is near to 50 is in yellow shades and the value which is near to 100 is in red shades.
Values which are closer to the limit have dark shades and the values which are far from limit have light shades. Let’s see a small patch of data and measure the different color scale value as shown below.
So this is how the color scaling can be done for Heat Map in any kind of data. We can choose different color scaling as well as per our color choice.
Example #2
There is another method of creating a Heat Map in Excel. This one is an advanced method of creating heat maps with some more features. For that, select the columns G and H, follow the same path as discussed in the above example.
- Go to Home menu, click on Conditional Formatting. Once we do that, we will get a drop-down list of it.
- Then click on Color Scales from the side list select More Rules as shown in below screenshot.
This will take us to the advance option of Formatting Rules for Heat Maps through Conditional Formatting. It will open the window of the New Formatting Rule window as shown below.
We can create a new rule or go ahead with existing rules. For the selected data set, let’s create a new rule for the heat map. As we can see, in Edit the Rule Description box, there is a Format Style tab available with a drop-down button. Select the 3 Color Scale as shown below.
Once we do that, we will see the 3 different color scale with value range which will appear as shown below. Here, all 3 colors have the default value set in it.
Now for complete range which is Minimum, Midpoint and Maximum, select the range by clicking on an icon
or we can simply write the number from which we need to see the Heat Map color scaling. As we can see below, here we have chosen 0 as Minimum percentile range, 50 as Midpoint percentile range and 100 as Maximum percentile range. And click on OK.
Note: We can change the color from the below color tab and check preview for how the color scaling will look like.
Once we click on OK, we will get the heat map created for selected data set as shown below.
As we can see above, with the help of selected color scaling, we got the Heat Map created which shows higher the data, more the dark will be the red color and lower the data, more light will be the green color. And Midpoint data is also showing the color change with respect to defined or selected value range. Here for selected Midpoint value 50, data is changing color to and fro.
Pros of Heat Map in Excel
- For a huge set of data where applying filter will not give the actual picture of data changes. There if we create Heat Map then we can actually see the change in value without applying the filters.
- For the data where we need to see the area (Region, Country, City, etc.) wise changes, in this case, using Heat Map gives high and safe points in the selected color scale.
- It is always recommended to use Heat Map when data size is huge and the pattern of data is fluctuating about some specific points.
Cons of Heat Map in Excel
- It is not advised to keep any function of Conditional Formatting applied in data for a long time. Because it makes excel work slow while we use the filter to sort the data.
Things to Remember About Heat Map in Excel
- Always clear the conditional formatting rule once its use is done. Or else save the rule so that it can be used later.
- Choose those color scaling only by Heat pattern which can be shown and measured easily.
- Measure the range before selecting, and fix the scaling points so that colors and data points can be fixed above and below those values.
Recommended Articles
This has been a guide to Heat Map in Excel. Here we discussed How to create Heat Map along with practical examples and downloadable excel template. You can also go through our other suggested articles–
