How to Create Heat Maps in Excel? (Step by Step with Example)

What is Heat Map in Excel?

Heat map in excel is a type of map that is created to show the representation of data in different colors, the colors show which area of the data needs to be focused on and which can be ignored, the intensity of the colors show the impact of the data series over the whole data set, the color scale in this type of chart varies from green to yellow to red, green color has been given the most importance whereas red has been given least importance.

Explained

Excel Heat Map is a type of representation, which helps us to compare the huge data as per the specifications. Heat Map in Excel is also called a data visualization technique. In technical terms, Heat Map is a type of visual representation of data, which represent the comparative view of data.

The heat map is compared to the data on a color scale, which varies from green to yellow to red. In this color scale, green color represents the high value, yellow will represent moderate value & red color represent low value.
In short, Heat Map is a visual representation of data with the help of colors in accordance with the values.
In this type of Heat map, we do representation on the basis of colors, which ranges from yellow to orange to red and so on. We can choose some specific colors based on our choices.

How to Create a Heat Map in Excel? (Step by Step)

We can create a simple excel heat map using the Conditional Formatting. Here are a few steps to create a Heat Map of a pool of data.

Step-1) First select the column of the data on which we want to apply the Heat map.
Step-2) Now, Got to Home tab, then go to Styles & Click on conditional formatting, then you will get a list. Now you have six different color combinations of color scale that will be available & you can select anyone to create a Heat Map in excel.
Step-3) After selecting conditional formatting, click on Color Scales from the list.
Step-4) Now you have six different color combinations of color scale will be available & you can select anyone to create a Heat Map in excel.

Examples

You can download this Heat Map Excel Template here – Heat Map Excel Template

Example #1

We have data from marks of the student for three months of sessional exams. In this data, compare the performance of students.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Step #1 – Data of mark of the student for three months is shown below:

Step #2 – Now, Select the column in which you want to create a Heat

Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.

Step #4 – Now Click on Color Scales from the list,

Step #5 – Now you have six different color combinations of color scale will be available & you can select anyone to create an Heat Map. See the figure below with the type of color combination.

Step #6 – Follow the same for the rest of the column & see the result below

Now let us learn with some more examples.

Example #2

We have data on the Quality Score of Advisors for a month of performance in sales. In this data, compare the performance of advisors for the given month.

Step #1 – Data of quality score for three months is shown below:

Step #2 – Now, Select the column in which you want to create a Heat

Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.

Step #4 – Now Click on Color Scales from the list,

Step #5 – Now you have six different color combinations of color scale will be available & you can select anyone to create a Heat Map. See the figure below with the type of color combination.

Example #3

We have data of the Average sale of Advisors for a month’s performance in sales. In this data, compare the performance of advisors for the given month

Step #1 – Data of quality score for three months is shown below:

Step #2 – Now, Select the column in which you want to create a Heat

Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.

Step #4 – Now Click on Color Scales from the list,

Step #5 – Now you have six different color combinations of color scale will be available & you can select anyone to create a Heat Map. See the figure below with the type of color combination.

Things to Remember

Heat Map is a visual representation of data that bifurcates data by colors ranging from high to low.
Colors can be chosen by the user.
Heat Map is basically conditional formatting done on a data, the data which applies on certain color has certain cell range color applied to it.