TL;DR: If you’re working with large datasets, Excel’s Power Map tool makes it easy to create a heatmap in Excel to spot trends, hotspots, and patterns. This blog walks you through the process step by step so you can transform raw data into meaningful visual insights.

Here’s what you’ll learn:

• What a heatmap chart in Excel is and when to use it.
• How to prepare and load large datasets into the Excel Data Model.
• Step-by-step instructions on how to make a heatmap in Excel with Power Map.
• Tips for filtering, customizing, and exploring your heatmap for deeper insights.

---

One of Excel’s lesser‑known but powerful features is the ability to create a heatmap in Excel using geographic mapping tools. In Excel 2016 (and with the right add‑ins in Excel 2013), you can turn large data sets into heatmap visualizations with Power Maps. The types of maps that can be generated with Power Maps are only limited by your imagination!

Here, we’ll show you how to make a heatmap in Excel using a dataset of police incidents in Seattle. Because the dataset is so large, we’ll lean on Excel’s Data Model to manage the data.

What Is a Heatmap Chart in Excel (and When to Use It)?

A heatmap chart in Excel is a way to visualize density or frequency. It shows “hotter” zones where something happens more often (e.g. more incidents). When paired with geographic mapping (i.e. Power Map), it becomes a spatial heatmap, letting you see where things happen most.

Use this type of heat map when you have a lot of geo‑tagged data (latitude/longitude, addresses, etc.) and want to spot clusters visually.

Accessing Power Map (3D Maps) in Excel

To create a heatmap in Excel, you first need to locate the Power Map / 3D Maps feature:

• Go to Insert → Tours → 3D Map in Excel.

If you don’t see the 3D Map icon, it might not be enabled in your version of Excel, or your license might not include it. You may need to install the add‑in, upgrade your Excel version, or activate the feature via your Office settings. This link can help you determine what is necessary to obtain Power Maps in Excel.

How to Create a Heatmap in Excel

Let’s begin the process of creating a heatmap using publicly available incident data from the Seattle Police Department. This data set has nearly 1.1 million rows, so it exceeds the limit of an Excel worksheet. Because of this, it will require the use of the Excel Data Model.

Step 1: Download and Prepare Your Data

We’re using the Seattle Police Department’s publicly available data. If you want to try this out on your computer, you can obtain the data from this link and export it to a CSV file for Excel.

Step 2: Load Data to the Excel Data Model

Because this file is larger than Excel’s row limit (1,048,576), we won’t load it directly into a sheet. Instead, we’ll use the Excel Data Model, here’s how to load the data into the Excel Data model:

1. In Excel, go to Data→ From Text/CSV and select the CSV file you saved locally to your computer. Click import.

   

2. In the import dialog, choose Load To (rather than just “Load”)

Note: if you select “Load,” the data will load into an Excel worksheet. In this case, some of the rows will be omitted because Excel’s row limit is 1,048,576. As a result, the resulting heat map will not be completely accurate.

 

4. In the Import Data window, Select the “Only Create Connection” and check “Add this Data to the Data Model.”

 

5. In the Data Tab, when the Queries & Connections icon is selected, the Seattle_Police_Department Query will appear in the panel on the right.

Step 3: Create the Heatmap

Now that the data is loaded, it is ready to use. For our purposes, we won’t worry about the preliminary steps of data exploration or cleansing. Instead, we will proceed directly to creating a heatmap of the number of incidents.

Here’s how to make a heatmap in Excel using Power Map:

1. To load the data into the map, select the 3D map icon in the Insert Tab. Once you’ve selected the 3D map you should see the following image:

 

2. 2. From the Field list, drag latitude and longitude over to the Location box.

3. 3. Drag the General Offence Number over to the Height box.
   4. In the Height box, select “Count” as the aggregate function.
   5. Drag Offence Type to the Filter box
   6. Select the Heatmap Icon (4th from left) under Data in the Layer panel

3. 7. Rename “Layer 1” (or whatever default name appears) to “Offenses” (or a name you prefer).

3. 8. Zoom into the Seattle area and view the heatmap.

Filtering & Interacting with the Heatmap

Now that your heat map is set up, you can gain further insights into the data by experimenting with layers and fields. Here are some ways you can interact with the heatmap:

• Use the Filter box to only show certain types of incidents (e.g. assaults, thefts).
• Zoom, pan, or rotate the map to explore different neighborhoods.
• Toggle layers on/off, or add additional fields (time, date, category) to slice the insights differently.

Tips, Troubleshooting & Best Practices

If you’re having trouble creating a heat map in Excel or extracting the information you’d hoped for, here are some tips that might help:

• Clean your data: ensure latitude/longitude values are valid, no blanks, and coordinates are correct.
• Watch performance: extremely large models or maps with many layers may slow Excel. Use filters or subsets when necessary.
• Use meaningful aggregation: count of incidents works, but sometimes sum or average of a metric is more useful.
• Styling matters: adjust color gradients, transparency, scale to make hotspots easier to interpret.
• Backup your work: sometimes maps or layers get reset, so save frequently.

If you get stuck on a specific step (e.g. map doesn’t display, filter isn’t working), reach out, our experts at ExcelHelp are happy to help.

Why Use Power Map over Static Heatmap Charts?

You might wonder, “Why not just use a conditional formatting heatmap or a static heatmap chart in Excel (e.g. in a grid)?” The difference:

• Grid heatmaps (coloring cells) are great for comparative tables, like highlighting high/low values in a table.
• But when you have geographic data and want to see where something is concentrated, a spatial heatmap in Excel via Power Map shines. This is a more dynamic heat map that gives you location context.

Want Help? Contact ExcelHelp for a Free Consultation

If this walkthrough got you excited but you’d rather have someone build it for you, you’re in the right place. At ExcelHelp, we design dashboards, automate workflows, and build advanced reports that fit your business.

Reach out today to schedule a free consultation. We’ll help you turn your data into insights (no headaches, no fuss).

Call 1‑800‑682‑0882 or fill out a contact form to submit your inquiry.