How to Create a Map Chart in Excel

Nothing tells a story with data quite like a map. Instead of just listing out sales numbers by state, you can instantly see which regions are your top performers. This guide will walk you through creating, customizing, and troubleshooting map charts in Excel, turning your geographical data into a clear and compelling visual.

What is a Map Chart and Why Should You Use One?

An Excel map chart, also known as a filled map or choropleth map, displays your data across geographical regions like countries, states, counties, or even postal codes. It uses colors or shades to represent values, making it easy to spot patterns and distributions at a glance. For instance, a darker shade of blue could represent higher sales, while a lighter shade shows lower sales.

These charts are incredibly useful for answering questions like:

• Which US states generate the most revenue?
• Which countries are driving the most website traffic?
• Where are our project delivery times the longest?
• How is our customer density distributed across different zip codes?

By plotting data on a map, you move beyond simple tables and give your numbers a powerful geographical context. It’s a simple shift that can reveal insights you might otherwise miss. Instead of telling your team "California sales are up 30%," you can show them exactly how California stands out on the national map.

Before You Begin: Getting Your Data Ready

Excel has made creating map charts straightforward, but it has one non-negotiable requirement: your data must be structured correctly. If your chart isn't working, data formatting is almost always the cause. Here's what you need to have in place.

Include Clear Geographic Data

Your dataset must contain at least one column of geographic data that Excel can recognize. This can include:

• Countries or Regions
• States or Provinces
• Counties
• Postal Codes (Zip Codes)

Excel uses Microsoft’s Bing map service to translate these text values into locations on a map. Be as specific as possible to avoid confusion. For instance, if you have data for "Washington," is that the state or the district? Providing a clarifying column (like "State" or "Country") can help Excel plot your data accurately. We'll touch more on this in the troubleshooting section.

Include Numerical Data

Your map needs something to measure. Beside each geographic location, you need a column with corresponding numerical values. This could be anything from sales figures and user counts to scores or percentages. This is the data that will determine the color shading on your map.

Format Your Data as an Excel Table

This is the most critical step. Raw data in cells won't work, you must format your range as an official Excel Table. This makes it easier for Excel to read and allows your chart to update dynamically if you add or change data later.

Don't worry, it's easy:

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. A dialog box will appear. Ensure the correct range is highlighted and that the "My table has headers" box is checked.
5. Click OK.

Your data range will now have colored banding and filter arrows in the headers, confirming it's an official Excel Table.

Here’s an example of a perfectly formatted table, ready for a map chart:

Step-by-Step Guide: Creating Your First Map Chart

Once your data is correctly formatted as a Table, creating the chart itself only takes a few clicks.

Step 1: Select Your Data

Click any single cell inside your Excel Table. You don’t need to highlight the whole thing, because it's a formatted table, Excel knows where your data starts and ends.

Step 2: Insert the Map Chart

Navigate to the Insert tab on the Excel Ribbon. In the Charts group, find and click on Maps, and then select Filled Map.

Note: If the Maps icon is grayed out, it may be because you are using an older version of Excel (Map charts are available for Office 365, Excel 2019, and newer) or your organization's security settings might have it disabled.

Step 3: Watch Your Chart Appear

Excel will take a moment to communicate with the Bing Maps service, send your geographic data (not your numerical data) to be identified, and then it will generate your map chart on the worksheet. The map will automatically zoom in on the relevant area and assign a default color gradient based on your values.

How to Customize Your Excel Map Chart

Your basic map is now ready, but the default settings might not be perfect. Excel gives you extensive options for refining your chart's appearance to make your story clearer.

Editing Chart Title and Legend

First things first. Double-click the Chart Title to give it a descriptive name. A good title tells viewers exactly what they're looking at, like "Q3 Revenue by State."

The legend is automatically created and is critical for helping people understand the color scale. You can click on it and drag it to a different position (top, bottom, corner) or resize it as needed.

Using the Chart Design Tab

When you select your chart, a special Chart Design tab appears on the Ribbon. From here, you can:

• Add Chart Element: Control elements like the Title, Legend, and Data Labels with more precision.
• Quick Layout: Apply pre-designed combinations of titles, legends, and labels.
• Change Colors: Choose from a range of predefined monochromatic color palettes that look professional.
• Chart Styles: Quickly apply different visual themes, including backgrounds and text effects.

Advanced Formatting with the Format Data Series Pane

For the most detailed customizations, you'll need the "Format Data Series" pane. Right-click directly on the map itself (on one of the colored regions) and select Format Data Series...

A pane will open on the right side of your screen with three key sections:

1. Map Projections

This setting changes the way the 3D globe is flattened into a 2D map. Your choices are:

• Mercator: A standard projection you often see. It preserves angles but distorts the size of areas near the poles (making Greenland look huge). Best for world maps where shape is important.
• Miller: A compromise that shows less distortion at the poles than Mercator.
• Albers: Perfect for showing a specific country or continent, as it accurately preserves the relative area of landmasses. This is usually the best choice when mapping states within a single country like the USA.

2. Map Area

This controls the zoom level of your map.

• Automatic: The default. Excel does its best to show all your data points.
• Only regions with data: The best setting. This zooms in tightly to only show the locations present in your data, hiding the rest of the world and making your chart cleaner.
• World: Zooms all the way out to show the full globe.

3. Series Color

This is where you gain full control over the map's coloring, which is essential for conveying the right message.

• Sequential (2-color): The default option. It's used for data that progresses sequentially from low to high, like sales from $0 to $500,000. You can choose a single hue that ranges from light (for lowest minimum value) to dark (for the highest maximum value). You can fully customize these colors to match your brand palette. For example, use a light green for the lowest values and a dark green for the highest ones.
• Diverging (3-color): This is incredibly useful for data that has a meaningful midpoint, like profit/loss data or performance against a target. For example, you could set red for negative profit, yellow for numbers around zero, and green for high profit. Excel lets you define the colors and values for the Minimum, Midpoint, and Maximum, giving you a clear visual for both positive and negative performance on a single map.

Common Problems and Troubleshooting Tips

Sometimes your map chart doesn't play nice. Here are solutions to the most common issues.

Problem: Some locations are blank or incorrect.

This is usually due to location ambiguity. Bing might not know which "Springfield" you mean. To fix this, add a helper column to your data to provide more context.

For example, if you're mapping US cities:

• Bad Data: A single column with "Paris"
• Good Data: Two columns, one for "City" (e.g., Paris) and one for "State" (e.g., Texas). After adding this, just include both columns in your Excel Table. Excel is smart enough to use the extra column for context.

Problem: The map chart says "We can't plot your data."

This happens when Bing can't recognize a high percentage of your locations. Double-check your geographical data column for misspellings (e.g., "Illnois" instead of "Illinois"). Even one incorrect letter can cause a location to fail. Consistency is also important, use "United States" or "USA" for all rows, not a mix of both.

Problem: The map chart area is grayed out or unusable.

This could be due to a few reasons:

1. Your Excel Version: Map charts are a modern feature. Ensure you're on a Microsoft 365 subscription or using a recent perpetual version like Excel 2019 or Excel 2021.
2. Outdated Data Type: Verify that your geographical data column is formatted as "Text" and your numerical column is formatted as "Number."
3. Connectivity: Because Excel uses Bing on the back end, you need to have an active internet connection for the chart to be created and updated.

A quick privacy note: While your numerical data stays on your machine, the geographical data (the city, state, or country names) is briefly sent to Bing to be identified and plotted. Keep this in mind if you're working with sensitive location information.

Final Thoughts

Excel map charts are a powerful feature for turning rows of geographical data into a clear visual story. Once you get your data properly formatted in an Excel Table, the process is fast and the customization options give you plenty of control to make your chart both insightful and presentation-ready.

And while creating charts one by one in Excel works well for specific reports, we know the real challenge often lies in pulling all that data together in the first place. That’s why we built Graphed. Instead of manually exporting sales data from Shopify, ad performance from Facebook, and traffic data from Google Analytics to build individual visuals, you can connect those sources to Graphed and create entire real-time dashboards just by asking questions in plain English. That way, your insights are always current, and you get hours back to focus on strategy instead of report-building.