Location Heat Map in Excel: How to Build One

How-to basics · Updated November 21, 2026

You can make a location heat map in Excel for Microsoft 365 on Windows with 3D Maps: put location fields and a numeric measure in a table, select a cell in it, then choose Insert > 3D Map > Open 3D Maps. In the map’s Layer Pane, assign the location fields, add the measure, and choose the Heat Map visualization. For areas such as countries or states, use a Filled Map chart instead; it shades regions rather than showing point-based intensity.

What a location heat map shows

A location heat map uses color intensity to show where a measured quantity is concentrated. For example, a retailer might map 1,200 service calls by address, or a public health team might compare reported cases by county. Darker or brighter areas generally indicate a higher value or denser cluster, depending on the map and its legend. It is a way to see spatial patterns that are hard to notice in a long list of rows.

The phrase can mean different things in Excel. A geographic heat map plots data against a real map. A heat map made with Conditional Formatting, by contrast, colors cells in a worksheet range; it does not know where those values are geographically. A Filled Map chart is geographic but colors predefined areas such as states or countries. Picking the right type first avoids trying to make one feature do another feature’s job.

One important distinction: a heat map does not necessarily mean that Excel has identified the exact address of each record. Excel’s map features use the location information in the source data to place or group records. The visual result is only as reliable as that information, and a dense cluster may represent many records close together rather than one unusually important point.

Choose the right Excel map

For individual addresses, cities, or other point locations, 3D Maps is the most direct built-in option for a heat-style geographic display. Its Heat Map layer emphasizes concentrations across a map. You can also use bubble or column visualizations when seeing individual locations or comparing values at specific places matters more than showing a smooth-looking concentration.

For values that belong to named geographic areas, a Filled Map chart is often clearer. It assigns a value to an area and fills that area with a color scale: for instance, sales by state or population by country. It does not create a continuous density surface from address points, and it cannot show neighborhood-level variation unless the map recognizes the smaller regions and the data supports them.

  • Use 3D Maps Heat Map for clusters or density from point-based location records.
  • Use a Filled Map chart for comparisons between recognized regions, such as states or countries.
  • Use Conditional Formatting for a quick color scale in worksheet cells, not a geographic visualization.

Prepare the location data

Start with a clean Excel table, ideally one record per event or observation. Useful columns might be Street Address, City, State or Province, Postal Code, Country, and Count or Sales. A table with separate fields is easier for Excel to interpret than a single ambiguous column containing entries such as Springfields, which occur in more than one place. Include country when locations span countries, and use consistent spelling and region names.

Each row needs a measure if the map is meant to compare intensity. For event data, each row can represent one event; for pre-aggregated data, a row might contain the total number of events for a city. Do not accidentally count an already aggregated total as though each row were a single event. Check duplicates and missing values, and confirm that numeric fields are stored as numbers rather than text.

For example, a store-visit table could contain City, State, Country, and Visits. If each row is a transaction, the map’s value should count transactions or sum a numeric visit field. If each row already gives one city’s monthly visit total, use that total as the measure. The map should answer a clearly stated question, such as where visits are concentrated in a selected month, rather than combine unrelated metrics.

Create a heat map with 3D Maps

  1. Click a cell in the prepared table. On the Windows desktop version of Excel for Microsoft 365, choose Insert > 3D Map > Open 3D Maps. If Excel asks, create a new tour.
  2. In the Layer Pane, drag or select the appropriate fields in the location category. Use the most precise dependable fields available, such as address and postal code, or city, state, and country. Excel may identify geographic fields automatically, but verify the assigned location categories.
  3. Add the numeric column to the value area. Check whether the aggregation is appropriate: a count, sum, or other offered summary can produce very different maps. For pre-summarized totals, sum the total field; for one row per event, a count may be appropriate.
  4. In the layer’s visualization options, choose Heat Map. Review the resulting map and its legend. Adjust the map view and available layer settings to make the pattern readable, then save the workbook.

3D Maps opens in a separate window and can present data on a globe or a flat map. You can add layers or use time fields to show change across dates, but a time animation is not necessary for a basic heat map. Keep the date range and filters visible or documented so another reader can tell what the display includes. 3D Maps is a Windows desktop feature; do not assume the same authoring workflow is available in Excel for Mac or Excel for the web.

Make a Filled Map for regional totals

When each row represents a named region rather than a point, select the region and value columns and choose Insert > Maps > Filled Map. Excel attempts to recognize the geography and shades regions according to their values. For example, a two-column table of state names and annual sales can produce a state-level comparison. If your table includes multiple countries, include a country column to disambiguate region names.

Map charts rely on geographic recognition and online map data, so a name that is unclear or unsupported may be left blank or assigned incorrectly. Use the chart’s legend and formatting options to make its scale understandable, and keep the data source and units clear in the chart title or nearby text. A Filled Map is not a substitute for a point heat map: it colors whole recognized regions, which can hide substantial differences within a state or country.

Worksheet heat maps are not geographic maps

If the goal is to highlight high and low values in a table, select the numeric cells and choose Home > Conditional Formatting > Color Scales, then pick a scale. Excel colors cells according to their values. This is useful for spotting unusually high sales by product and month, for example, but it does not plot the data on a map or interpret addresses. Avoid calling this a location map unless the worksheet itself is arranged as a meaningful geographic grid.

Improve accuracy and readability

Geocoding errors are a common source of misleading maps. Use more complete location fields when possible, standardize abbreviations, and check ambiguous place names. For high-stakes reporting, compare a sample of plotted locations with known addresses or a trusted geographic source. Excel’s interpretation of a text location is not proof that the point is correct.

Think carefully about what the color represents. A heat layer may make a busy urban area appear intense simply because many observations are close together; that does not automatically mean the rate per resident, per customer, or per square mile is high. If the question concerns rates, calculate or provide a suitable rate as the mapped measure rather than relying on raw totals. Explain the denominator and time period in the chart notes.

Also consider privacy. A map of precise home addresses, patient visits, or other sensitive records can expose individuals, especially in sparsely populated areas. Aggregate locations to an appropriate area, remove identifying details, and follow your organization’s privacy rules before sharing a workbook. Restrict access to the source data as well as the finished visualization.

Common problems and practical fixes

  • Locations appear in the wrong place: add state, province, or country fields and verify the location category assigned in the Layer Pane. A city name by itself may not be unique.
  • The map is blank or incomplete: check for blank location cells, inconsistent spellings, unsupported areas, and internet access where the map feature needs online geographic services.
  • The heat looks too broad or too faint: review the aggregation and layer settings, and zoom to the area of interest. A statewide view can conceal neighborhood clusters, while a close view can exaggerate local differences.
  • Totals look too large: check whether the source already contains totals and whether Excel is summing or counting the intended field. Confirm the result against a small manual subtotal.
  • The feature is missing: confirm that you are using Excel desktop for Windows and a supported Microsoft 365 installation. Excel for Mac and Excel for the web may offer different map capabilities, and neither should be assumed to include the Windows 3D Maps workflow.

When another tool is a better fit

Excel is convenient for a quick map from a modest table, especially when the data already lives in a workbook. If you need custom geographic boundaries, detailed geographic analysis, frequent interactive dashboards, or precise control over map styling, a dedicated GIS application or a business intelligence tool may be more suitable. Power BI, for example, offers separate mapping visualizations and is not the same feature as Excel 3D Maps.

For a shareable report, test the workbook on the recipient’s version of Excel before relying on the map. A chart or map that renders on one desktop may not behave identically in a browser or on another platform. If recipients only need a fixed picture, export or capture a clearly labeled image; if they need to filter or inspect underlying values, provide an accessible data table and explain which Excel features are required.

Frequently Asked Questions

Can Excel make a heat map from addresses?
Yes. In Excel for Microsoft 365 on Windows, use 3D Maps, assign the address-related fields as locations, add a numeric measure, and choose Heat Map. Check the plotted locations because incomplete or ambiguous addresses can be geocoded incorrectly.

What is the difference between a heat map and a Filled Map in Excel?
A 3D Maps heat layer shows concentration from location records. A Filled Map chart shades recognized geographic areas according to values assigned to those areas. Use the first for point-based clusters and the second for comparisons such as sales by state.

Why can’t Excel find my location?
The location may be ambiguous, incomplete, misspelled, or unsupported. Add higher-level context such as state and country, use separate columns for location components, and verify the field’s geographic category. Also check whether the Excel feature can access its required online map services.

Can I make a location heat map in Excel for Mac?
The 3D Maps workflow described here is for Excel desktop on Windows and is not available in the same way on Mac. Excel for Mac may support other map features depending on the version, but those features do not necessarily provide the same point-based heat layer.

Can I make a heat map without a map?
Yes. Select a numeric range and use Home > Conditional Formatting > Color Scales to color cells by value. That creates a worksheet heat map, not a geographic visualization.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.