Excel Heat Map (Table of Contents)
Heat Map in Excel
Heat Map in Excel is used to show data in different color patterns. 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. In addition, 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 Columns G and H for two months. Now we want to apply Heat maps in the data shown below.
For that, first, select both the columns G and H. Now go to the 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 the Red-Yellow-Green color pattern, as shown below.
Once we do that, we will see the heat pattern to all selected color scales, 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 closer to the limit have dark shades, and the values far from the limit have light shades. Let’s see a small patch of data and measure the different color scale values as shown below.
So this is how the color scaling can be done for Heat maps 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. Select the columns G and H and follow the same path as discussed in the above example.
- Go to the 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 the below screenshot.
This will take us to the advanced 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 scales with a value range, as shown below. Here, all 3 colors have the default value set in them.
Now for the 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 the Minimum percentile range, 50 as the Midpoint percentile range, and 100 as the Maximum percentile range. And click on OK.
Note: We can change the color from the below color tab and check the preview for how the color scaling will look like.
Once we click on OK, we will get the heat map created for the 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, the more the dark will be the red color and lower the data, the more light will be the green color. And Midpoint data is also showing the color change with respect to the defined or selected value range. Here for selected Midpoint value 50, data is changing color to and fro.
Pros
- For a huge set of data where applying a filter will not give the actual picture of data changes. If we create Heat Map, 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
- 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 a downloadable excel template. You can also go through our other suggested articles–
