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 is using conditional formatting. With it, you’ll see different colors or shades of colors based on the values in your cells.

1. To get started, select the cell range you want in the heat map without any column or row headers.

2. Go to the Home tab, open the Conditional Formatting drop-down menu, and move to Color Scales. As you move your cursor over the 12 options, you can see a preview of each applied to your data.

3. Choose the one you want to use, and you’ll see your data update.

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

Use Custom Colors

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.

1. Select the data you want to include, go to the Home tab, open the Conditional Formatting drop-down menu, and pick New Rule.

2. When the dialog box opens, choose the first option at the top for Format all cells based on their values.

3. In the bottom section, use the Format Style drop-down box to pick either the 2-Color Scale or 3-Color Scale.

4. Then, complete the details beneath:
5. Type: Choose the value types for the Minimum and Maximum as well as Midpoint if you use the 3-Color Scale.
6. Value: Choose or enter the corresponding value for the Type you pick above it. For instance, if you select Percentile in the Type section, enter the percent in the Value section.
7. Color: Choose the color for each Type or pick More Colors to select a custom shade.

8. As you enter the details, you’ll see the Preview update so you know how your cells will appear.

9. When you finish, select OK to apply the conditional formatting heat map to your dataset.

Remove the Number Values

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 the number values regardless of which conditional formatting rule you set up above.

1. Select the cells containing the numbers. Either right-click and pick Format Cells or open the Number drop-down menu on the Home tab and pick More Number Formats.

2. In the Format Cells box, choose Custom on the left. Then, below Type on the right, enter ;;; (three semicolons) and select OK.

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.

1. Select the data for the map and be sure to include the location names. For example, you can see below that we select the state names and the corresponding values.
2. Go to the Insert tab, open the Maps menu in the Charts section, and pick Filled Map.

3. When the map chart displays, you should see your values represented with colors and a legend, both of which you can edit and customize.

4. Depending on the data you display and where your regions are located, you can adjust the map area, color scale, legend, and more. For general options, select the chart and use the Chart Design tab that displays. Here, you can add, remove, and edit the chart elements, adjust the layout, change the color scheme, and pick a different style.

5. For data series options, double-click the series on the chart to open the Format Data Series sidebar. Confirm that the Series Options tab is selected and then expand the Series Options and Series Color sections to make your changes as described below.

6. Series Options: Use the Map Area drop-down menu to show only the regions containing data. This is helpful if you’re displaying only a few states in the U.S., for example. You can also use the Map Labels menu to add those labels.

7. Series Color: Use the drop-down box to pick between two and three colors for the data. You can then pick the data types for the minimum and maximum and the colors you want to use for each.

8. You’ll see your map update with each change, making it easy to undo an edit if needed. When you finish, simply close the sidebar with the X on the top right.

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.

1. Select the data for your map including the location names and optionally the column and row headers. Go to the Insert tab, choose 3D Maps in the Tours section, and pick Open 3D Maps.

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

2. The Layer Pane should automatically open on the right. If not, select this button on the Home tab in the ribbon.

3. Expand the Data section in the pane and pick Heat Map.

4. Select Add Field in the Location box and choose the location data. For our example, this is State.

5. Select Add Field in the Value box and choose the value data. For our example, this is Rank.

6. You should see your locations and values plotted on the 3D map as a heat map. Use the plus and minus buttons to zoom in and out or the directional arrows to move the map. You can also select and drag to spin the map.

7. To change the colors, expand Layer Options. Then, use the Color Scale, Radius, Opacity, and Colors tools to make your adjustments.

8. You can also use the ribbon tools on the Home tab to pick a theme, add a text box, create a video, and more.
9. To place the map in your Excel sheet, select Capture Screen in the ribbon on the Home tab. This places a screenshot of the map on your clipboard.

10. You can then paste the image in your worksheet using Paste on the Home tab or the keyboard shortcut Ctrl + V.

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 when an Excel graph just doesn’t fit.

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