How to Create a Heat Map in Excel

If you have data that you want to display graphically, but a chart isn’t quite right, try a heat map. You can create a heat map in Excel to present your data in an easy-to-read way.

What Is a Heat Map?

A heat map is a visual representation of data using colors. This can be a diagram or map where the colors correspond to the number values in a dataset.

The most common example of a heat map is when you watch the weather report on your favorite news channel. You may see temperatures across the country or region showing red for hot, orange for warm, and yellow for cooler temperatures, for instance.

This type of visual is useful when you have a wide range of values spanning several categories. As examples, you may have sales for store departments across years or averages of student grades across weeks or months.

Here, we’ll show you a few different ways to make a heat map to attractively display your data.

Create a Heat Map With Conditional Formatting

The simplest way to create a heat map in Excel isusing conditional formatting. With it, you’ll see different colors or shades of colors based on the values in your cells.

As you can see in our example, we have the highest values in red and lowest values in green making both easy to spot.

While the preset Color Scales give you basic red, blue, yellow, and green, you may want to use either a specific color set or only two colors. For this, you can create a new formatting rule to your specifications.

Because heat maps are visualizations of your data, you may want to remove the numbers in the cells and use only the colors. You might do this if the values are distracting or less meaningful than the colors.

You can remove thenumber valuesregardless of which conditional formatting rule you set up above.

When you return to your data, you should see the numbers gone but the corresponding colors for the heat map remain.

Create a Geographical Heat Map

If your data relates to locations like states, regions, or countries, you can take your heat map up a level and use a geographical map chart. This still displays your values as color-coded indicators but plots them on a map with the matching locations.

You then have your geographical heat map to display your data as a nice visual.

Create a 3D Geographical Heat Map

Another way to add a geographical heat map but with advanced options is using Microsoft Excel’s 3D Maps feature. With this, you have a 3D world map that you can spin and zoom. This option is useful if you want several layers or filtered data.

Note: If you’ve used this feature previously, you’ll need to pickStart New Tourto open a new map.

Will You Turn Up the Heat?

Heat maps are terrific visuals for displaying data in color form instead of with numbers, percentages, decimals, or dollars and are ideal whenan Excel graphjust doesn’t fit.

Are you going to make a heat map in Excel? If so, let us know which method you choose.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How to Create a Heat Map in Excel#

What Is a Heat Map?#

Create a Heat Map With Conditional Formatting#

Create a Geographical Heat Map#

Create a 3D Geographical Heat Map#

Will You Turn Up the Heat?#