How to Map Data in Excel

Transforming rows of location data into a visually compelling map might seem like a job for specialized software, but you can do it right inside Microsoft Excel. Whether you're tracking sales by state or website traffic by country, creating a data map in Excel is a powerful way to spot geographic trends and tell a clear story. This article will guide you through two primary methods for mapping your data, from a simple filled map to a more dynamic 3D visualization.

First Things First: Prepare Your Data

Before you can create any map, your data needs to be in a clean, structured format that Excel can understand. Proper preparation is the most important step and will save you a lot of headaches later on.

Organize Your Data in a Table

The best practice is always to format your data as an official Excel Table. This helps keep everything organized and makes it easier for Excel’s tools to recognize your data fields. To do this, simply click any cell within your data range and press Ctrl + T (or go to Insert > Table).

A good data set for mapping should look something like this:

Make sure you have:

• Clear, descriptive column headers (e.g., "State," "Sales," "Country").
• One row for each unique location. All data for California should be in one row, for instance.

Using Recognized Geographic Data Types

Excel's mapping features work by cross-referencing your locations with Bing Maps. For this to work, you need to use recognizable location types. Excel can easily map:

• Country or Region
• State or Province
• County
• City
• Postal or ZIP Code

Things like specific street addresses or latitude/longitude coordinates are better suited for the 3D Maps feature, which we'll cover later.

Resolve Ambiguities

Sometimes, a location name can be ambiguous. For example, "Springfield" is a city name in many different states. If your data just says "Springfield," Excel might not know which one to map. To avoid this, include additional columns to provide context, like a "State" or "Country" column. This helps Excel correctly identify and plot your data points.

Method 1: Creating a Basic Map with Excel's Map Chart

The fastest way to visualize geographic data in Excel is by using the built-in Map Chart. This feature is perfect for creating a filled map, also known as a choropleth map, where different regions are shaded or colored based on a corresponding value.

Step-by-Step Guide to Creating a Map Chart

Let's use an example of sales data by U.S. states.

1. Select Your Data: Click any single cell inside your data table. You don’t need to highlight the entire range, Excel is smart enough to detect the table.

2. Insert the Map Chart: Navigate to the Insert tab on the Ribbon. In the Charts group, click on Maps, and then select Filled Map.

Excel will automatically generate a map based on your data. By default, it will use a sequential color scale, where higher values are represented by a darker shade.

Customizing Your Map Chart

Once your map is created, you'll probably want to tweak its appearance. To do this, right-click on the map and choose Format Data Series. This will open a settings pane on the right side of your screen with several useful options.

Map projection

This changes the way the 2D map represents the 3D globe. You can typically choose between Mercator, Miller, or Albers. For global views, Mercator is common, but play around to see which one best fits your data.

Map area

This lets you control the zoom level.

• Automatic: Excel tries its best to center and zoom in on your locations.
• Only regions with data: This is the preferred setting. It zooms in to precisely frame just the locations you've included, removing the distraction of other continents or regions.
• World: Displays the entire world map.

Map labels

Here you can decide whether to show location names on your map. Your options are None, Best fit only, or Show all. "Best fit only" is usually a safe choice to avoid a cluttered look.

Series Color

This is where you control the look and feel of your data visualization. You can choose:

• Sequential (2-color): The default, which uses a light-to-dark gradient of a single color. It's great for showing values that range from low to high.
• Diverging (3-color): This option is excellent for showing data that has a negative, neutral, and positive range. For example, you could show states with sales losses in red, states near the average in yellow, and high-performing states in green.

Method 2: Going Deeper with Excel's 3D Maps

If you need more power and interactivity, or if you want to map specific addresses (using latitude and longitude), Excel's 3D Maps feature is the way to go. Formerly known as Power Map, this tool feels like a separate application inside Excel and allows for creating layers, animations, and stunning visual tours.

How to Get Started with 3D Maps

Using the same data, follow these steps:

1. Select Your Data: As before, click a cell within your data table.

2. Launch 3D Map: Go to the Insert tab, and click on 3D Map. If this is your first time, you might be prompted to enable the add-in. Click Open 3D Maps.

A new window will open with a globe. On the right, you'll see a panel where Excel has tried to match your columns to geographic fields. Verify these are correct. For example, ensure your "State" column is mapped to the "State/Province" location type.

Working with Layers and Visualization Types

In 3D Maps, you build visualizations using layers. You can add multiple data sets or visualizations on top of each other.

By default, 3D Maps will create a visualization based on your data. You can control this in the Layer Pane:

• Location: Drag your geographic fields here (e.g., State, City).
• Height: This adds a numerical value to your locations, often shown as a bar or column. Drag a metric like "Sales" here.
• Category: Use this to segment your data. For example, you could show sales broken down by product category within each state.
• Time: If your data has a date/time field, you can add it here to create an animation of how things changed over time.

You can also choose between several visualization types for your data:

• Stacked Column: The default, shows magnitude as column height.
• Heat Map: Displays data as colored hotspots, ideal for showing density.
• Bubble: Represents values with the size of a circle.
• Region: Similar to the filled map, this colors entire areas like states or countries.

Creating a Presentation with Tours and Scenes

One of the most powerful features of 3D Maps is the ability to create a "tour." A tour is a series of "scenes," each with its own camera angle, data layers, and annotations. You can guide viewers from a global view down to a specific state, explaining trends along the way. When you're done, you can even export the tour as a video file to share with others.

Best Practices for Effective Data Maps

Regardless of the method you choose, follow these tips to make your maps clear and impactful.

• Keep it Simple: Don't try to cram too much information onto one map. If you have several stories to tell, consider creating multiple maps.
• Choose Colors Mindfully: Use color schemes that are intuitive. A light-to-dark gradient is easy to understand for scaled values. Avoid using too many distinct colors, as it can be confusing.
• Use a Clear Title and Legend: Your map should be understandable at a glance. A descriptive title and a clear legend are essential for providing context.
• Don't Be Afraid to Tweak Your Data: Sometimes, grouping smaller regions can make a map more readable. For example, instead of mapping every European country separately, you could group them into regions like "Western Europe" or "Scandinavia" if that better suits your analysis.

Final Thoughts

Excel provides surprisingly robust tools for bringing geographic data to life. The basic Map chart is perfect for quick, high-level dashboards and reports, while 3D Maps unlocks the ability to create dynamic, layered stories with your data. By taking a few minutes to clean your data first, you can easily create insightful visualizations that reveal patterns you might have missed in a spreadsheet.

Of course, getting data into Excel is often the biggest hurdle. Frequently, you need to pull reports from tools like Google Analytics, Shopify, Salesforce, or your ad platforms, and then stitch them together manually. At Graphed, we’ve built a solution to this exact problem. Our tool connects to all your marketing and sales data sources, allowing you to create real-time, interactive dashboards by simply describing what you want in plain English. Instead of spending hours exporting CSVs and formatting tables, you can get the insights you need in seconds.