Location Heat Map in Excel: Create One Step by Step

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, you can create a location heat map with 3D Maps: organize your location data in a table, select it, choose Insert > 3D Map > Open 3D Maps, then set the layer’s visualization to Heat Map. For shading whole areas such as states or countries by a value, use a Filled Map chart instead; it answers a related question but is not the same kind of heat map.

What Excel Means by a Location Heat Map

A location heat map puts geographic records on a map and uses color intensity to show where records are concentrated. For example, a delivery company could plot thousands of delivery addresses to see where activity clusters, or a nonprofit could map service visits to identify neighborhoods with more demand. The display makes spatial patterns easier to spot than a list of addresses.

Excel offers two map features that are easy to confuse. 3D Maps can display location points as a heat map layer, which is suited to showing clusters or density. A Filled Map chart shades recognized geographic regions according to a data value, such as sales by state. It is a choropleth map: the color represents the value for each area, not a cloud of nearby point records. The right choice depends on whether your data describes individual places or totals for defined regions.

Choose the Right Excel Mapping Method

Use 3D Maps when each row represents a place or event and you want to explore where records gather. It can geocode location fields and present them over a map. Use a Filled Map chart when your rows already contain one value per geographic area, such as one row per country, state, county, or postal region. A filled map is often clearer for comparing totals across regions.

  • Many addresses or coordinate pairs: Start with 3D Maps and its Heat Map visualization.
  • One total per state or country: Use Insert > Maps > Filled Map.
  • A colored grid of cells in a worksheet: Conditional formatting can create a value heat map, but it does not place values geographically.
  • Advanced interactive dashboards or large datasets: Consider Power BI or a dedicated geographic information system if Excel’s mapping controls are not enough.

These methods are not interchangeable. A filled map will not automatically turn a list of customer addresses into a point-density layer, and worksheet conditional formatting will not recognize geography or draw a map.

Prepare Location Data Before Mapping

Good results depend on clear, consistent source data. Put one record in each row and use a header in every column. A useful table might contain Customer, Street, City, State, Postal Code, Country, Visit Date, and Visit Count. Select the data and use Insert > Table to turn it into an Excel table; a table is easier to extend when new records are added.

Excel has a better chance of identifying locations when you supply enough context. A city name by itself can be ambiguous: there may be multiple places with that name. Include the state or province and country, or use latitude and longitude when you have reliable coordinates. Keep latitude and longitude in separate numeric columns, and check that the values have not been imported as text. Do not swap the two columns: latitude is north-south and longitude is east-west.

Before mapping, check for blank locations, misspellings, inconsistent abbreviations, duplicate rows, and dates or values stored as text. Decide what one row represents. If a row is one customer, a count of rows measures customers only if each customer appears once. If a customer can appear multiple times, the map may represent visits or transactions instead. That distinction affects what the colors mean.

Create a Heat Map with 3D Maps

In Excel for Microsoft 365 on Windows, prepare and select a cell in your source table, then choose Insert > 3D Map > Open 3D Maps. Excel opens the 3D Maps window and creates a tour with a layer. If the 3D Map command is unavailable, check that you are using the Windows desktop application and that your Excel edition supports the feature; it is not available in Excel for Mac or Excel for the web.

  1. In the Layer Pane, add the location columns to the Location area. Use the most specific fields you have, such as Street, City, State, and Country, or assign Latitude and Longitude to their corresponding fields.
  2. Wait for Excel to geocode the locations. Review any locations it cannot place and add more geographic detail if needed.
  3. In the layer’s visualization controls, choose Heat Map. Adjust the map view and any available layer settings to make dense areas distinguishable.
  4. Inspect the result against your source records. Zoom in and out, check unusual clusters, and confirm that locations were placed in the intended country or region.

3D Maps is a separate mapping window, not a normal worksheet chart. You can build multiple layers and scenes in a tour, change the view, and play a tour to present a sequence of map views. The Heat Map display emphasizes concentrations, while other visualizations such as bubbles or columns can be useful when you need to inspect individual points or compare quantities at locations.

What the Colors Represent

A heat map is a visual summary, not a precise boundary or a substitute for the underlying data. Hotter colors generally indicate a stronger concentration in an area, but the appearance depends on how close records are, the zoom level, and the layer’s display settings. A dense-looking patch does not necessarily mean the region has a high rate of activity; it may simply contain more records or a larger population.

Be particularly careful when comparing places with different population sizes or coverage. Ten service requests in a small town and ten in a large city are the same count but not the same per-person rate. If the question is about intensity relative to population, calculate or prepare a rate outside the map, using an appropriate denominator, and explain how that rate was defined. Do not infer causation from a colored cluster.

Also distinguish record counts from weighted totals. If each row is one event, point density can communicate where events are concentrated. If each row has a value such as sales or units, first confirm that the map visualization is displaying the intended measure. For a dependable comparison of totals by area, aggregate the records to the areas you want to compare and use a Filled Map chart or another suitable chart. A map’s appearance alone should not be treated as proof that it has summed a field correctly.

Make a Filled Map for Regional Values

For a regional comparison, create a small summary table with a geographic name and its value—for example, State and Orders. Select the table and choose Insert > Maps > Filled Map. Excel shades the regions it recognizes. The chart works best when geographic labels are unambiguous; include a country or other higher-level field where relevant, and keep names consistent.

A Filled Map chart is useful for answering questions such as which states have the highest sales total. It is not a street-level map, and it does not show the exact positions of individual stores or customers. The color scale can also affect interpretation: a skewed data range may make modest differences look dramatic or make most regions appear similar. Review the legend, labels, and range before sharing the chart.

Map charts rely on online geographic services to identify and draw regions, so internet access may be required. Availability can depend on your Excel version, account, and connected experiences or organizational settings. If the chart does not render, check connectivity and your organization’s Office privacy settings rather than assuming the source table is invalid.

Common Problems and How to Fix Them

  • Locations appear in the wrong place: Add state, province, or country information, or use verified coordinates. A city name without context can match the wrong place.
  • Some rows are missing: Check blank cells, malformed coordinates, nonstandard postal codes, and records Excel could not geocode. Review the layer’s location results and correct the source data.
  • The map looks like one large hotspot: Zoom in, check for duplicate records, and confirm the intended unit of analysis. A repeated address may represent many events rather than a single place.
  • Colors do not match expected totals: Verify whether the display is showing record concentration or a numeric measure. For totals by state or country, aggregate first and use a Filled Map chart.
  • The map feature is missing: 3D Maps is a Windows desktop feature, not a feature in Excel for Mac or Excel for the web. Update or switch to a supported Windows desktop version if your organization permits it.

When a location fails to resolve, avoid silently deleting it. Keep a separate list of unresolved rows and record how they were handled. If a small number of records cannot be placed, disclose that limitation when the map is used for a decision.

Privacy, Sharing, and Practical Limits

Location data can be sensitive, especially when it identifies customers, patients, employees, or home addresses. Use only data you are authorized to process, remove personal identifiers that are not needed for the analysis, and follow your organization’s rules for cloud-connected Office features. Mapping may involve online services, so check applicable privacy and information-security settings before using confidential data.

Maps are also affected by the quality and coverage of the source data. An address list that omits rural visits, for example, can make service demand appear lower in those areas. A heat map may reveal clusters but does not explain why they exist. Pair it with counts, rates, dates, and relevant context, and retain the table so another person can check the summary.

For sharing, save the workbook in a format supported by the people who need to open it and verify that the map displays on their devices. 3D Maps tours are not a substitute for a universally portable image or report. If recipients use Mac or web versions, provide a static screenshot or a separate supported visualization when interactive 3D Maps cannot be opened.

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 fields to Location, and choose the Heat Map visualization. Include enough address detail for Excel to distinguish places, and review unresolved or incorrectly geocoded records.

Can I create a location heat map in Excel for Mac?

Excel for Mac does not include 3D Maps. Depending on your version, you may be able to create a Filled Map chart for regional totals, but that does not provide the same address-level heat map. You can use Excel for Windows for 3D Maps or use another mapping tool.

What is the difference between a heat map and a Filled Map chart?

A heat map layer emphasizes concentrations of point locations, such as a cluster of store visits. A Filled Map chart shades whole geographic regions according to a value, such as total sales by state. Choose based on whether your data is a set of places or a set of region-level summaries.

Why are some of my locations plotted incorrectly?

Excel may have matched a place name to a different location, especially when the name is common or missing its state or country. Add geographic context or use accurate latitude and longitude, then check the map against known locations.

Can I use sales or visit counts as the heat map intensity?

Do not assume the heat map automatically weights each location by a numeric column. Confirm what the selected layer is using. If you need a specific sum, average, or rate by region, aggregate the data to that level and map the resulting values with a suitable chart.

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.