How to Create a Geographical Map in Excel

Ever look at a spreadsheet full of city, state, or country names and think, "I wish I could just see this on a map"? You're in luck. Excel's built-in map chart transforms your geographical data into a powerful, insightful visualization that's way more intuitive than a simple table. This guide will walk you through creating, customizing, and troubleshooting these helpful maps.

Why Use an Excel Map Chart?

While tables and bar charts are useful, they can't show geographic patterns like a map can. A map chart in Excel turns rows of location data into a colored heat map, making it easy to spot trends, concentrations, and outliers at a glance. It’s the perfect tool for answering questions like:

• Which states generate the most revenue?

• Where are our website users coming from?

• What regions have the highest concentration of customers?

• Are our marketing campaigns performing better in a specific geographical area?

Instead of just telling someone your top five states for sales, you can show them, instantly providing context about regional performance.

Step 1: Get Your Data Ready for Mapping

This is the most important step. Your map chart will only be as good as the data you give it. Excel is smart, but it needs your information to be structured in a clean, predictable way. A messy dataset is the number one reason maps fail to generate correctly.

Best Practices for Structuring Data

Your data should be in a simple table format. You need at least two columns:

1. A Location Column: This is where you put your geographic data. It could be countries, states/provinces, counties, or postal codes.

2. A Metric Column: This contains the numeric data you want to plot on the map, like sales figures, user counts, or conversion rates.

Here’s an example of a well-structured dataset for plotting U.S. sales by state:

Example Data:

Pro Tip: Format Your Data as an Excel Table

While not strictly required, formatting your data as an official Excel Table makes things much easier (and is a general best practice for data analysis in Excel). A Table automatically expands as you add new rows, ensuring your map chart source data is always up to date.

To do this:

1. Click anywhere inside your data range.

2. Go to the Insert tab on the Ribbon.

3. Click Table (or use the shortcut Ctrl + T).

4. Make sure the range is correct and check the box for "My table has headers."

5. Click OK.

Your range will now have colored banding and filter arrows, indicating it's an official Table.

Step 2: Create a Geographical Map in Excel

Once your data is clean and formatted, creating the map is surprisingly simple. You're just a few clicks away.

1. Select Your Data: Click any cell inside your data table. You don’t need to highlight the entire thing, especially if you formatted it as a Table.

2. Insert the Map Chart: Navigate to the Insert tab on the Ribbon. In the Charts group, find and click on the Maps icon (it looks like a small globe). Then, select Filled Map.

And that’s it! Excel will take a moment to communicate with the Bing Maps service, geocode your locations, and generate a map chart on your spreadsheet.

Step 3: Customize Your Map Chart for Clarity and Impact

You've got a map, but the default settings might not be exactly what you need. Excel provides several options for customizing the look and feel of your geographical chart.

First, click on your map chart to bring up two new contextual tabs on the Ribbon: Chart Design and Format.

Using the Chart Design Tab

This tab controls the overall look and feel. You can quickly:

• Add Chart Element: Add, remove, or change the position of the chart title, legend, and data labels (the numbers on the map).

• Quick Layout: Choose from predefined layouts that arrange the title and legend in different ways.

• Change Colors: Select from a gallery of built-in color palettes to match your brand or report style.

• Chart Styles: Apply a preset style that combines colors, shadows, and other effects for a more polished look.

Fine-Tuning with the Format Pane (The Real Power)

For more granular control, right-click on the map itself (not the chart background) and select Format Data Series.... This opens up a sidebar pane with specific mapping options.

Series Options

This is where you'll find the most important map-specific settings:

• Map Projection: This changes how the curved surface of the Earth is represented on a flat map. You can choose between Automatic, Mercator, Miller, or Albers. For most business dashboards showing the whole world, Mercator is a familiar and safe choice.

• Map Area: This lets you control the zoom level. Automatic is usually best, but you can force the map to show the entire World, focus on a specific Country/Region, or show Multiple countries/regions. Setting it to "Only regions with data" is a great way to zoom in on just the areas you are reporting on.

• Map Labels: Want to see the names of the states or countries? You can change this from None to Show all to make the map easier to read without having to hover over each region.

Series Color

This setting controls the "heat map" effect. By default, it uses a sequential color scale where lower values are a lighter shade and higher values are a darker shade of the same color.

You have more options here:

• Sequential (2-color): The default. Good for showing a range from low to high, like 0 sales to a maximum sales number. You can customize the specific colors for Minimum and Maximum values.

• Diverging (3-color): This is perfect when your data has a meaningful center point, like showing profit and loss or performance against a target. You can set colors for Minimum (e.g., red for losses), a center point (e.g., yellow for breaking even), and Maximum (e.g., green for profits).

Common Problems and Troubleshooting

Sometimes your map won't display correctly. Don't worry, it's usually due to a handful of common data issues that are easy to fix.

Problem: My Map is Blank or Shows an Error!

This is often caused by ambiguous location names. For example, there are dozens of places named "Salisbury" around the world, and many countries have a "Washington County."

The Fix: Add another column with higher-level geographic data to give Excel more context. If you have city data, add a "State" or "Country" column.

Instead of just 'Springfield', your table should have columns for both 'City' AND 'State':

Then, when you create your map, make sure you select all three columns. Excel will use the additional information to pinpoint the correct location.

Problem: Some of My Regions Aren't Colored In.

This happens if Bing Maps can't recognize a location name.

The Fix: Check for typos in your location column. A simple misspelling like "United Kingom" instead of "United Kingdom" will cause it to be ignored. Also, be aware of naming conventions (e.g., "United States" vs. "USA"). It's best to use the official, full name of a place.

If you've done everything correctly and a location is still missing, it might be a region that Bing’s mapping service doesn't recognize or resolve at the desired level of detail.

Final Thoughts

Creating a geographical map chart in Excel is a straightforward process that can instantly add another layer of insight to your reports. The key is in the preparation - a clean, organized, and unambiguous data table is the foundation for a great map. Once your data is set, you can generate and customize insightful visuals in less than a minute.

While an Excel map is great for creating static visualizations, the process can become a bit manual if you need to routinely pull fresh data from tools like Google Analytics, Shopify, or your CRM. At Graphed, we specialize in automating this process entirely. You can connect your marketing and sales sources in seconds and simply ask questions in plain language like, "Show me a map of website conversions by state for the last 90 days from Google Analytics." Our AI generates a live, interactive map that updates automatically, saving you from the routine of downloading and preparing new datasets just to build the same reports.