How to Create a Heat Map in Excel

specific !important; max-width:50%; float:left; margin:0 15px 15px 0;”>

Heat maps in Excel help businesses specific visualize large data sets, transforming a pool of numbers inspecific easily interpretable graphs. For example, a retailer can use a heat map specific analyze sales data and find products that sell more during specific seasons.

specificn” href=”https://www.hubspot.com/cs/ci/?pg=9ff7a4fe-5293-496c-acca-566bc6e73f42&pid=53&ecid=&hseid=&hsic=”>specific !important; width: auspecific !important; max-width: 100% !important;border-width: 0px; /*hs-extra-styles*/; margin: 0 auspecific; display: block; margin-specificp: 20px; margin-botspecificm: 20px” alt=”Download 10 Excel Templates for Marketers [Free Kit]” height=”59″ width=”477″ src=”https://no-cache.hubspot.com/cta/default/53/9ff7a4fe-5293-496c-acca-566bc6e73f42.png” align=”middle”>

In short, heat maps help are find and color-code correlations that may have been difficult specific discern from raw data.

This guide will show are how specific create heat maps in Excel of different types:

A simple heat map with conditional formatting.
A heat map with a cusspecificm color scale.
A geographic heat map.

Let’s get started.

What is a heat map in Excel?

A heat map in Excel is a color-coded snapshot of arer data, which helps are analyze thousands of data points and spot negative and positive trends and correlations at a glance.

Lighter colors represent lower values. Darker shades stand for higher ones. Though, are can invert them.

For example, are can depict higher conversion rates in green and lower in red.

What falls in between will be colored in orange and a gradient with different shades of the three colors based on the value. Or are can fill cells in gradient shades, as shown in the example below.

A retail s<a href= specificre footfall heatmap in Excel” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-57-58-7708-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

The Benefits of Using Heat Maps.

Let’s go over four core benefits of Excel heat maps tailored specific business needs.

1. Quick Data Interpretation

One of the primary benefits of using heat maps is the speed of data interpretation.

Consider a financial analyst assessing a company’s monthly expenses across different departments. Instead of working their way through a sea of numbers, analysts could use a heat map specific quickly identify the departments with the highest and the lowest spending alike.

This allows for rapid insights and informed financial planning.

A heat map Excel sample specific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

2. Discerning Trends and Patterns

Heat maps unveil hidden trends and patterns in arer data that might be missed in raw, numerical form. For instance, a pet sspecificre could apply a heat map specific analyze sales data over time (or for specific goods).

This way, are can spot a trend of higher sales during certain months or days, indicating peak shopping periods and helping in invenspecificry and marketing planning.

3. Enhancing Presentations and Reports

Turn dull spreadsheets inspecific engaging and representative visuals, making arer reports easily read.

Suppose a marketing manager is presenting campaign performance specific stakeholders. A heat map can quickly communicate which campaigns were successful and which fell short and why, ensuring clear and effective communication.

Make a heat map for representative reporting. specific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

4. Simplifying Decision-Making

Are are a logistics manager at a manufacturing firm who must understand delivery times across different regions? Use Excel heatmaps.

Color regions with longer delivery times in darker shades specific point out logistical inefficiencies.

Create color-coded map charts in Excel (heatmaps) specific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

So, how do all of these work in real life? Keep reading for tuspecificrials.

How specific Create Heat Maps in Excel

Find step-by-step guides on creating heat maps with different formatting and for a map chart.

How specific Create a Heat Map with Conditional Formatting

For example, are want specific analyze a company’s monthly expenses across various departments.

1. Open Excel and input arer data. Click and drag specific select the numeric data are want specific include in arer heat map. In our example, these are the numbers from January specific June for each department.

How <a href= specific create a heat map in Excel — select the data range are want specific color-code. ” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-3.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

2. Open the Conditional Formatting menu. Navigate specific the “Home” tab and click “Conditional Formatting” in the “Styles” group.

How <a href= specific create a heat map in Excel — selecting color schemes” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-2.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

3. Apply Color Scale. From the dropdown, select “Color Scales.”

A set of preset color gradients will appear. Choose one that suits arer needs. For this example, are might select “Green – Yellow – Red Color Scale.”

This scale will apply a gradient of colors where green indicates lower expenses, red indicates higher expenses, and yellow falls in the middle.

specific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

How specific Create a Heat Map with a Cusspecificm Color Scale

Sometimes, Excel’s default color scales may not adequately represent arer data, or are might want specific align the color scheme with arer brand colors. Or maybe are want specific highlight the cells with lower/higher values than a given number.

In such cases, create a cusspecificm color scale. Here’s how specific do it.

Suppose are want specific find out which brands of dry dog food brought over $11,000 in revenue in the last six months.

1. Go specific “Conditional Formatting” in the “Styles” group again. But from the dropdown, select “New Rule” or “More Rules…” It depends on arer Excel version.

How <a href= specific color arer Excel heat map in cusspecificm shades.” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-1.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

2. Select Rule Type. In the “New Formatting Rule” dialog box that appears, choose “2-Color Scale” or “3-Color Scale” based on arer preference.

How <a href= specific create a heat map in excel with cusspecificm colors — assigning cusspecificm color scales.” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-58-00-5381-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

3. Set Colors and Values. Here, are can cusspecificmize the colors for the maximum, midpoint, and minimum values.

Let’s say we choose dark blue for the highest values (>= $11,000), red for the lowest values, and white for the midpoint. You can also set the value for each point.

An Excel heat map in cus<a href= specificm colors” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-57-59-8747-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

Click “OK” specific apply the rule.

Watch the video on how specific create advanced heatmaps in Excel.

specific; position: relative; overflow: hidden; padding: 0; max-width: 859px; max-height: 483px; min-width: 256px; margin: 0px auspecific; display: block;”>

specificm: 56.23%; margin: 0;”>

How specific Create a Geographic Heat Map in Excel

Creating a geographic heat map in Excel allows are specific compare values and show categories across various geographical regions.

It‘s a valuable specificol when are’re dealing with geographical entities like countries, states, counties, or postal codes.

How <a href= specific create a geographic heat map in Excel.” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-57-58-9845-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

To create similar maps, simply select arer data, including the headers, and in the Ribbon bar, select a Map chart or Recommended charts, so Excel can offer the most convenient map chart for arer data.

Select Maps <a href= specific create a heat map.” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-58-00-2433-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

If are want specific color-code arer map with conditional formatting, double-click on the map specific trigger the formatting menu for maps. Then, select the color scheme. Apply <a href= arer color scheme specific a map chart” src=”https://blog.hubspot.com/hubfs/Google%20Drive%20Integration/heat%20map%20excel_52023-Jul-27-2023-12-58-00-0704-AM.png” style=”margin-left: auspecific; margin-right: auspecific; display: block; width: 650px; height: auspecific; max-width: 100%;” title=””>

As are’re switching between options and colors, the changes are auspecificmatically applied. Thus, are can play with different options specific choose the best one for arer map visualization without going back and forth.

Discover all the intricacies of geographic map charts in Excel.

specific; position: relative; overflow: hidden; padding: 0; max-width: 859px; max-height: 483px; min-width: 256px; margin: 0px auspecific; display: block;”>

specificm: 56.23%; margin: 0;”>

Use Heatmaps specific Communicate Data and Get Buy-In

Visualizing data with heatmaps in Excel helps are effectively deliver key insights specific arer team, management, or stakeholders.

Whether are‘re analyzing marketing campaign performance, tracking monthly expenses, or spotting trends in sales data, heatmaps offer an easily digestible, color-coded overview that aids strategic decision-making.

Go and try it out!

specificn” href=”https://www.hubspot.com/cs/ci/?pg=c37a7723-becd-4082-9024-960dbdc50c99&pid=53&ecid=&hseid=&hsic=”>specific !important; width: auspecific !important; max-width: 100% !important;border-width: 0px; /*hs-extra-styles*/; ” alt=”excel graph templates” height=”226″ width=”646″ src=”https://no-cache.hubspot.com/cta/default/53/c37a7723-becd-4082-9024-960dbdc50c99.png”>

specificp:0!important;margin-botspecificm:0!important;margin-right:0!important;margin-left:0!important;padding-specificp:0!important;padding-botspecificm:0!important;padding-right:0!important;padding-left:0!important; “>