6 Ways to Create a Heat Map in Microsoft Excel

Learn how to create a heat map in Excel with real-world data, easy methods, and effortless steps.

Microsoft Excel offers some of the best data visualization techniques known so far to data scientists, analysts, mathematicians, and statisticians. Among these heat maps are a great tool to visualize data in a 2D plane with color gradients.

When the value increases in certain blocks or sectors of the data, the intensity of certain colors also increases proportionately. Thus, you can easily figure out which block holds the most values.

For example, as a retail store owner, you want to find out which products sell more in specific seasons. With a heat map, you can easily identify the products visually rather than scanning tables containing many rows of data with your eyes.

Join me as I explain the most popular and practical methods to plot Excel heat maps.

Create Excel Heat Map Using Conditional Formatting

The Color Scales cell formatting rules of Excel Conditional Formatting allow you to create heat maps instantly, provided that you select a supported dataset.

Your data must contain values, like revenue, sales, scores, etc., that can be distinguished clearly. Besides whole or fractional number values, you could also have percentages, ratios, and more.

Sample dataset for heat map
Sample dataset for heat map

For example, the above dataset is perfect for visualizing how the countries compare with each other across metrics like Oil & Gas, Food Grains, Clothing, Telecom, and more.

If you’ve got such a dataset, go to your worksheet and highlight all the rows and tables containing values.

Color Scales rules
Color Scales rules

Click the Conditional Formatting drop-down menu. Now, highlight your cursor over the Color Scales menu and click on any of the color combinations available within 12 sets.

Excel shall automatically generate the heat map on the target dataset according to the values in the cells and columns.

The Color Scales rule applies a weighted average system to each cell comparing the columns. Therefore, the intensity of a shade increases either with the increase or decrease of a particular value.

📒 Read More: How To Make Your Data Visually Appealing

Heat Map Without Numbers

Format Cells
Format Cells

If you wish to hide the values of your heat map, highlight the cell ranges containing the numerical values.

Press Ctrl + 1 to bring up the Format Cells dialog.

Navigate to the Custom number formatting category from the left side navigation panel.

Click the Type field and type this code: ;;;. Click OK to apply the number formatting modification.

Removed numbers from heat map
Removed numbers from heat map

Excel shall hide the numbers on the cells of your heat map.

📒 Read More: How to Add Sparklines in Microsoft Excel

Create Excel Heat Map Using a Custom Color Scale

If the built-in Color Scales rules don’t accurately visualize your dataset into a heat map, you can customize the underlying configurations. Thus, your heat map becomes more precise and easily understandable.

By personalizing the Color Scales rules, you can also figure out the color codes used for the lowest, median, and highest points. You can put the color codes in a table as a legend so that, the audience can know how to interpret the intensities of different shades on the heat map.

Let me show you how to create a custom Color Scales rule for your dataset.

More Rules option
More Rules option

Highlight the target dataset and click the Conditional Formatting button. In the context menu, hover the mouse cursor over Highlight Cells Rules and click on the More Rules option at the bottom of the overflow menu.

Format all cells based on their values
Format all cells based on their values

Excel shall show the New Formatting Rule dialog. Here, click on the Format all cells based on their values selection to bring up the Edit the Rule Description menu at the bottom of the dialog box.

In Edit the Rule Description, you need to customize the following fields according to your input dataset:

Format style
Format style
  • Format Style: Choose between the 2-Color Scale and the 3-Color Scale options. In the 3-Color Scale, you can customize the cell fill color for three points of the whole dataset, namely Minimum, Midpoint, and Maximum. Therefore, the 3-Color Scale option visualizes the dataset better than the 2-Color Scale option.
Type in Color Scales
Type in Color Scales
  • Type: Click the Type drop-down menus below Minimum, Midpoint, and Maximum, and choose options like Lowest Value, Percentile, and Highest Value.
Choosing color in Color Scales
Choosing color in Color Scales
  • Color: Click on the Color drop-down menus for Minimum, Midpoint, and Maximum to select which shade should denote which value ranges.

Once done configuring the custom Color Scales rule, click the OK button to apply the cell formatting styles to your dataset.

Using a custom Color Scales
Using a custom Color Scales

Finally, Excel creates the heat map for you. Create a table to indicate the colors and the value ranges they represent.

📒 Read More: How to Make a Pie Chart in Microsoft Excel

Create Excel Heat Map Using a Contour Chart

Suppose, you’re analyzing a topographic dataset. You’d like to visualize the data in a heat map of 3-dimensional space. In this scenario, you can use the Contour chart from the Surface category of the Change Chart Type tool.

Example dataset for Contour chart
Example dataset for Contour chart

The input dataset shouldn’t contain more than three columns of numerical values since a Controur chart plots data in X, Y, and Z dimensions. Also, the input dataset should have both column headers and row headers. An example dataset is shown above.

You could also include more than three columns. However, in this case, the output Contour chart becomes more complex.

Recommended Charts
Recommended Charts

Select the input dataset and click on the Insert tab. Find and click on the Recommended Charts button.

All Charts
All Charts

You shall now see the Insert Chat dialog. There, navigate to the All Charts tab.

Surface Contour chart
Surface Contour chart

Click on the Surface category in the left-side navigation panel.

A Contour chart
A Contour chart

On the right side, click on the Contour option. Click OK to create the chart.

Switch Row Column
Switch Row Column

After generating the chart, you can customize it from the Chart Design tab. For example, click on the Switch Row/Column button to swap the data over the axis.

Customize data source
Customize data source

Alternatively, you can click the Select Data button on the Chart Design tab to customize the data range included in the current chart object.

A more complex Contour chart
A more complex Contour chart

Find above a more expansive Contour chart created from 8 data columns.

Create Excel Heat Map Using Maps

If your input dataset contains geographic data, you can use the Excel Maps chart to create a heat map. Since the data is plotted on a map of the geographies or locations included in the dataset, your audience can easily grasp the underlying message you want to deliver.

Example dataset for Excel maps
Example dataset for Excel maps

An example dataset suitable for the Excel Maps chart is as given above. You can include the names of the states and one of the data columns, like Population (millions) to create a heat map to understand population density by color coding on a 2D map of the United States.

Once you’ve created the heat map for the Population (millions) data column, you can then use other data, like Literacy Rate (%) and GDP (in billions $) for two separate heat maps.

Now, instead of presenting a tabular data comparison of US States by Population (millions), Literacy Rate (%), and GDP (in billions $), you can present the heat maps for better audience attention and comprehension.

Excel Maps chart
Excel Maps chart

Creating a Map chart is pretty straightforward. Select your dataset and click on the Maps drop-down inside the Charts block of the Insert tab.

Filled Map
Filled Map

In the Maps drop-down, choose the Filled Map option.

Excel shall automatically choose the map and plot the heat map by analyzing the geographies in the input dataset.

Microsoft Excel uses the Bing Maps API to create the heat map. So, you might get a prompt to allow Excel to share your dataset with Bing Maps.

By default, Excel uses the Sequential (2-color) option to visualize the chart data series. You can change it to Diverging (3-color) to make the heat map more comprehensive. Your audience shall see three levels of data points used to colorize the states on the map, like the lowest value, the middle value, and the highest value.

Diverging (3-color)
Diverging (3-color)

To modify the Series Options, double-click on the colored map to bring up the Format Data Point navigation panel on the right side of the Excel app.

There, click on the Series Color drop-down and switch to the Diverging (3-color) option.

Fill color drop down
Fill color drop down

Here, you can customize the color coding for the states by clicking the cell fill color drop-down menus for the Minimum, Midpoint, and Maximum fields.

Clicking on the heat map
Clicking on the heat map

To plot the next data column, you don’t need to start from scratch. Once you’re done exporting the first chart as an image, click on the map once. This should highlight the input data ranges.

Literacy Rate (%) heat map
Literacy Rate (%) heat map

Click on the edge of the Population (millions) dataset and drag it to the next data column. This should plot the data points of the Literacy Rate (%) column on the same map.

Format data series for second column
Format data series for second column

To customize this map, bring up the Format Data Series console as explained earlier.

Create Excel Heat Map Using 3D Map

Often you might not find the exact data insight you’re looking for in a 2D heat map. In such a situation, you can use Excel 3D Maps. This feature is only available in Excel 2013 or later editions.

Open 3D Maps
Open 3D Maps

Highlight the input data, preferably geographic data, and click on the 3D Map drop-down in the Tours block of the Insert tab.

Now, click on the Open 3D Maps option from the context menu that opens.

Excel 3D Maps tool
Excel 3D Maps tool

Excel 3D Maps tool shall open. There, you shall see a blank map of the US on a 3D globe.

Heat map
Heat map

Click on the Add Layer button to get the Data panel. There, click on the Heat Map tab.

States in Location
States in Location

Click on the Add Field button inside the Location field. Choose States from the drop-down menu. Excel captures the names of the states from the input dataset.

Now, click on the Add Field button inside the Value field and choose any one of the column data in your input dataset, like Population (millions). Excel 3D Maps shall create a basic heat map.

Change Layer Options
Change Layer Options

To make this heat map better, click the Layer Options drop-down menu and increase the values for the following fields:

  • Color Scale
  • Radius of Influence
Color customizations
Color customizations

If you wish to change the shading of the heat map, click the Default drop-down menu in the Colors section and choose Custom.

You can change the Low and High colors by clicking on the fill color drop-down boxes. You can also click the Add Color button to add a midpoint for the data points being plotted in the heat map. Now, you can add a third color to the heat map.

Capture screen
Capture screen

To export the heat map to another app, like Microsoft Word, you can click the Capture Screen button in the Tour block of the Home tab.

Copied 3D map to Word

Now, go to the destination Word file and press Ctrl + V to paste the copied data as an image.

Create Excel Heat Map Using Excel VBA

If you wish to avoid the manual steps of selecting the data range, finding the heat map tool, and configuring it to a finished visualization, you can use Excel VBA. A VBA macro can help you automate this task.

Find below a simple script you can use:

VBA script to create heat map
VBA script to create heat map
Sub CreateHeatMap()
    ' Prompt the user to select a range of cells
    Dim rng As Range
    Set rng = Application.InputBox("Select a range", Type:=8)
    
    ' Prompt the user to choose between 2-color and 3-color heat map
    Dim colorScale As Integer
    colorScale = Application.InputBox("Enter 2 for 2-color heat map, or 3 for 3-color heat map", Type:=1)
    
    ' Apply the chosen color scale to the selected cells
    With rng.FormatConditions
        .Delete
        Select Case colorScale
            Case 2
                .AddColorScale(2)
                .Item(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .Item(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)
                .Item(1).ColorScaleCriteria(2).Type = xlConditionValueHighestValue
                .Item(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 0, 0)
            Case 3
                .AddColorScale(3)
                .Item(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .Item(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 255, 255)
                .Item(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
                .Item(1).ColorScaleCriteria(2).Value = 50
                .Item(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
                .Item(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
                .Item(1).ColorScaleCriteria(3).FormatColor.Color = RGB(255, 0, 0)
        End Select
    End With
End Sub

To set up a VBA macro using the above script, follow the instructions outlined in this article:

📒 Read More: How To Use The VBA Code You Find Online

Create Heat Map Macro
Create Heat Map Macro

If the macro is ready, press Alt + F8 to get to the Macro dialog. Select the CreateHeatMap macro and hit Run.

Input box for dataset
Input box for dataset

You must enter the source dataset in the prompt that follows.

Input box for color scale
Input box for color scale

Then, you can also choose the color coding system of the heat map.

Excel heat map using VBA
Excel heat map using VBA

Finally, Excel reformats the source dataset to a heat map.

Conclusions

So far, you’ve explored six different methods to create a heat map in Excel. If you need to apply this skill occasionally in a small dataset, you can use the Conditional Formatting-based methods.

If your input dataset contains geographical and demographic data, use Excel Maps or Excel 3D Maps-based methods.

Finally, if you wish to create heat maps frequently for a large dataset, use the Excel VBA-based method.

Comment below to let me know which method you liked the most. Also, mention below if you know a better technique to create a heat map in Excel.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃