Heat Map in Excel

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 that 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, the 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)

Heat Map Example 1-3

We can create a simple excel heat map, conditional formatting in excel. Here are a few steps to create a Heat Map of a pool of data.

Below are steps to create heat map in excel –

  1. First, select the column of the data on which we want to apply the Heat map.

  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.

  3. After selecting conditional formatting, click on Color Scales from the list.

  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 the marks of the student for three months of sessional exams. In this data, compare the performance of students.

  • Step #1 – Data of mark of the student for three months is shown below:
Heat Map Example 1
  • Step #2 – Now, Select the column in which you want to create a Heat
Heat Map Example 1-1
  • Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.
Heat Map Example 1-2
  • Step #4 – Now Click on Color Scales from the list,
Heat Map Example 1-3
  • 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.
Heat Map Example 1-4
  • Step #6 – Follow the same for the rest of the column & see the result below
Heat Map Example 1-5

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:
Heat Map Example 2
  • Step #2 – Now, Select the column in which you want to create a Heat
Heat Map Example 2-1
  • Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.
Heat Map Example 2-2
  • Step #4 – Now Click on Color Scales from the list,
Heat Map Example 2-3
  • 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.
Heat Map Example 2-4

Example #3

We have data on 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:
Example 3
  • Step #2 – Now, Select the column in which you want to create a Heat
Example 3-1
  • Step #3 – Now, go to the home tab, then go to Styles & click on conditional formatting, then you will get a list of options.
Example 3-2
  • Step #4 – Now Click on Color Scales from the list,
Example 3-3
  • 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-4

Things to Remember

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

Recommended Articles

This has been a guide to Heat Map in Excel. Here we discuss how to Create Heat Map in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *