Open In App

How to Create a Heat Map in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

Heatmaps are basically used to calculate the intensity of an event. It helps us in easily understanding and interpreting any complex dataset and make deductions from it. It uses colors in the bar graph of a dataset and shows different regions with different colors, which area needs more attention and which needs less attention.

Now heatmaps in excel can be easily created in any of the following two ways :

  • Manually coloring each cell depending upon its intensity with different colors.
  • Using Conditional formatting

Usually, the first method is tiring as we have to do manual work, so we can use the latter method of conditional formatting to generate a Heatmap. The steps required for generation of heatmap using conditional formatting are given below.

Generation of HEATMAP using CONDITIONAL FORMATTING :

Follow the below steps to generate HeatMap using conditional formatting:

  • Create a dataset in Excel

Creating a dataset in Excel

  • Select the data for which you want conditional formatting

Selecting B4 to D13 dataset in excel for formatting

  • Go to Home Tab -> Conditional Formatting -> Color Scales , Now choose any of the default format , you will get a heatmap generated

Steps for conditional formatting and final generated heatmap of the dataset

  • We can choose from the default color scales , or we can go to More Rules option and find more options available for generation of heatmap

Conditional formatting – More options tab

Practical Applications of Heatmaps :

  • We can use it to describe the intensity of various values in a dataset – like in the covid dataset above , we can use heatmap to check which countries are more affected and which are less affected.
  • Application in the retail matrix – Let there be many departmental stores for a MNC in a downtown , we can use heatmap to check which stores receives more supplies so as to better allocate in-store resources.
  • Application in the Population Map – We can use Heatmaps for the population maps to get visual representation about various parameters like population density , per-capita income , average temperatures and much more.

Last Updated : 30 Jul, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads