Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Create an Excel Heat Map? 5 Simple Steps

How to Create an Excel Heat Map? 5 Simple Steps

(Note: This tutorial on Excel heat map is suitable for all Excel versions including Office 365)

In Excel, charts and graphs are widely used to present data in an easy to understand and visually intuitive format. The heat map is one such very popular data presentation tool in Excel.

Heat Map in Excel
Heat Map in Excel

In this guide, I’ll show you how to create an Excel heat map from scratch the easy way. 

You’ll learn:

  1. What is an Excel Heat Map?
  2. How to Create a Heat Map in Excel Using Conditional Formatting
    • Step 1: Enter the Source Data
    • Step 2: Apply Conditional Formatting
    • Step 3: Hide the Numbers
    • Step 4: Use a Single Color Gradient in Heat Map (Optional)
    • Step 5: Format your Heat Map into a Table

You can watch this short video to easily understand how to create a heat map in Excel.

What is an Excel Heat Map?

A heat map is actually a colour coded table. It conveys a lot of information easily by combining a normal table with a colour palette. I am sure, most of us would have come across heat maps in our daily lives. They are used in reports to draw attention to certain data points and explain them easily. 

Heat maps in Excel, use colours to differentiate data, thus helping you give a bird’s eye view of what’s happening.  

The value in each cell of an Excel heat map has a colour and it conveys some meaning. It usually ranges from dark to light colour and represents the weightage of the value in the cell. 

In the next section, let us see how to create a heat map in Excel,  using conditional formatting. We’ll also see how to create a dynamic heat map that updates itself when new rows or columns of data are added.

Let’s get started.

Related:

Easily Make a Bullet Chart in Excel—2 Examples

Creating a Dynamic Pivot Chart Title Based on Slicer(6 Easy Steps)

How to Make a Line Graph in Excel? 4 Best Line Graph Examples

How to Create a Heat Map in Excel Using Conditional Formatting

You can technically create a heat map in Excel, by manually changing the colour of each cell as we add data to the table. But, this is a static method, since the colour will not change if there is any variation in the data. 

Instead, we can use conditional formatting to highlight the cells based on their values. It changes the colour in the cell automatically when new data is included or already present data is changed.

Let us see how to do this in Excel:

Step 1: Enter the Source Data

Enter your source data, in the normal format. This will act as the base of your heat map. Ensure that all the rows and columns of your source data are properly labelled to avoid confusion.

Enter the source data for the Excel heat map
Enter the source data for the Excel heat map

Step 2: Apply Conditional Formatting

Select the range of cells (source data) where you wish to apply the heat map. Then, go to Home > Conditional Formatting > Color Scales and pick any suitable colour scale. 

Select the source data and pick a suitable colour scale under Conditional Formatting
Select the source data and pick a suitable colour scale under Conditional Formatting

In this example, the Red – Yellow – Green colour scale applies green colour to lower values and red to higher values. Anything in between will get a shade of yellow mixed with green and red. . There is a gradient with different shades for the 3 colours.

A Heat map in Excel with Red - Yellow - Green colour scale
A Heat map in Excel with Red – Yellow – Green colour scale

Also Read:

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Make a Scatter Plot in Excel? 4 Easy Steps

How to Add Error Bars in Excel? 7 Best Methods

Step 3: Hide the Numbers

If you don’t want the numbers to appear in your heat map, select the numbers and press the Ctrl+1 shortcut to open the Format cells dialog box. There, go to the Custom category of the Number tab. Now, type the code: ;;; in the text box and click OK. 

Type in ";;;" in the Custom section of the Number tab to hide the numbers in your heat map
Type in “;;;” in the Custom section of the Number tab to hide the numbers in your heat map

Congratulations! You have successfully created a heat map in Excel. 

A heat map in Excel to track seasonality in sales
A heat map in Excel to track seasonality in sales

Step 4: Use a Single Color Gradient in Heat Map (Optional)

Sometimes, you don’t want a multi-colour heat map and instead want a single colour to be highlighted for all the lower values, for the sake of simplicity.

To do this, go to Home > Conditional Formatting > Color Scales > More Options.

Click on More Rules...
Click on More Rules…

In the New Formatting Rule box, select ‘2-Color scale’ from the Format Style dropbox.

Set 2-Color Scale in Format Style
Set 2-Color Scale in Format Style

Now, set any single colour’s lighter and darker hues in the Minimum and Maximum color pickers, as per your requirements.

Set a single colour's light and dark hues in the Minimum and Maximum colour pickers
Set any single colour’s light and dark hues in the Minimum and Maximum colour pickers

That’s all. Click OK, to view your single colour gradient heat map.

Excel heat map in single colour gradient
Excel heat map in single colour gradient

Step 5: Format your Heat Map into a Table

An Excel heat map is dynamic by default since it reads the cell values and changes colour accordingly. But, if you notice carefully, it will not update itself, if new rows of data are added.

To make it truly dynamic, format your entire data as a table. You can easily do this, by selecting the entire heat map (including the headers) and using the Ctrl+T shortcut. This way, your heat map will update automatically, if newer rows of data are inserted.  

Heat maps in Table formatting, update new rows automatically.
Heat maps in Table formatting, update new rows automatically.

Suggested Reads:

How to Add a Watermark in Excel? 2 Easy Methods

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

Let’s Wrap Up

In this article, we learned how to create a heat map using conditional formatting, in five simple steps. We hope you find this guide helpful. I recommend you test this out, in a practice sheet to gain a better understanding.

Please feel free to ask any questions in the comments section. We are always happy to help. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Create a Fillable Form in Word

Top Ten Excel Functions you HAVE to know.

How to Extract an Excel Substring? – 6 Best Methods

How to Recover Unsaved Excel File? – 3 Ways

How to use EDATE in Excel? 11 Easy Examples

Using the Windows 8 Firewall

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)