How to Create a Heat Map in Excel?
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
Share your thoughts in the comments
Please Login to comment...