EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Heat Map
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel Heat Map

By Ashwani JaiswalAshwani Jaiswal

Heat Map in Excel

Excel Heat Map (Table of Contents)

  • Heat Map in Excel
  • How to Create a Heat Map in Excel?

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

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.

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

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.

Heat Map in Excel - Example 1-1

For that, first, select both the columns G and H. Now go to the Home menu and select Conditional Formatting as shown below.

Heat Map in Excel - Example 1-2

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.

Heat Map in Excel - Example 1-3

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.

Heat Map in Excel - Example 1-4

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.

Result of Example 1

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.

Heat Map in Excel - Example 2-1

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.

Heat Map in Excel - Example 2-2

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.

Heat Map in Excel - Example 2-3

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.

3 different color scale
Now for the complete range, which is Minimum, Midpoint, and Maximum, select the range by clicking on an iconMidpoint, 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.

Set Value

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.

Result of Example 2

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–

  1. Roadmap Template in Excel
  2. Map Chart in Excel
  3. 3D Maps in Excel
  4. Surface Charts in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Heat Map Excel Template

EDUCBA

Download Heat Map Excel Template

EDUCBA

डाउनलोड Heat Map Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more