How to Plot Data on a Map in Excel

Turning a spreadsheet full of city, state, or country names into a compelling visual map can instantly reveal trends that were hiding in plain sight. Instead of just seeing rows of data, you can see where your customers are, which regions are driving the most traffic, or how your sales are distributed across the country. This article will walk you through exactly how to plot your geographical data on a map directly within Excel.

First, Get Your Data Ready for Mapping

Before Excel can work its magic, you need to set up your data in a way it can understand. This is the single most important step, and getting it right will save you a ton of headaches later. Poorly formatted data is the number one reason Excel maps fail.

1. Use an Official Excel Table

While you can create a map from a simple range of cells, converting your data into an official Excel Table makes it much more reliable and easier to manage. Tables automatically expand to include new data and work seamlessly with Excel's charting features.

• Simply click any cell within your data range.
• Go to the Insert tab on the Ribbon.
• Click Table (or use the shortcut Ctrl+T).
• Make sure the "My table has headers" box is checked if you have column titles (like "State," "Sales," "Country," etc.).

Your data will now be formatted in a clean, professional-looking table.

2. Include Clear Geographic Data Columns

Excel needs at least one column with geographical information to plot your data. This can include things like:

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

You also need at least one column with numerical data you want to visualize, such as sales figures, a number of customers, website sessions, or store locations (use a "1" in the column for each location to count them).

Here’s an example of a simple, well-structured table for mapping sales data by U.S. state:

Sales by State

3. Clean and Standardize Your Location Data

Excel uses Microsoft's Bing map service to convert your text (like "California" or "CA") into a location on a map. This process can fail if your data is messy. Be consistent!

• Fix Misspellings: "Claifornia" won't map correctly. A quick spelling check can go a long way.
• Be Consistent: Don't mix full state names and abbreviations in the same column (e.g., "California" and "TX"). Stick to one format. Using abbreviations (TX, NY, CA) is often more reliable.
• Avoid Ambiguity: A city named "Springfield" exists in many states. If you're plotting city data, make sure to include a corresponding "State" or "Country" column to provide context and help Excel pinpoint the correct location.

Method 1: Creating a Filled Map Chart (The Quick and Easy Way)

For most day-to-day needs, the built-in Filled Map chart is perfect. It color-codes different regions (countries, states, counties, or ZIP codes) based on the values you provide. Higher values get a darker shade, and lower values get a lighter one, making it easy to spot hotspots at a glance.

Step-by-Step Instructions

1. Select Your Data: Click any single cell within your data table. You don't need to highlight the entire table, Excel is smart enough to know what you want.
2. Open the Maps Menu: Go to the Insert tab on the Ribbon. In the "Charts" section, click on Maps, and then select Filled Map.
3. Generate the Map: Excel will automatically create a map chart and place it on your worksheet. It will analyze your headers and data to make its best guess at what you want to plot. For our example, it will recognize the state names and use the Revenue data to shade each state.

That's it! In just a few clicks, you have a professional-looking map visualizing your sales distribution.

Customizing Your Filled Map

Once your map is created, you can easily customize it to better tell your story.

• Change the Color Scheme: Double-click the map to open the "Format Data Series" panel on the right. Under "Series Options," you can change the color theme from the default blue to a sequential (e.g., a green gradient) or diverging (e.g., red-yellow-green) color scheme.
• Adjust Map Projections: In the same panel, you can change the projection of the map (e.g., Mercator, Miller, Albers) to find one that best displays your data.
• Control Map Labels: You can choose to show all geographical labels, no labels, or only the ones that best fit on the map.

What If a Location Shows "No Data"?

Sometimes you’ll see locations in gray with a “no data” label. This often happens if there's an ambiguity Excel can't resolve. For instance, if you're plotting a "Washington" data point, it might not know if you mean Washington state or Washington, D.C. Creating a separate column for state and mapping that instead, or including a more specific location column (state, country), can usually fix this.

Method 2: Using 3D Maps for More Dynamic Visuals

If you want to create something more interactive and visually stunning, Excel's 3D Maps feature (once known as Power Map) is your best friend. This tool lets you plot data as columns, heat maps, or bubbles on a 3D globe or a flat map, making it ideal for presenting data or creating animated tours that show how data changes over time.

Step-by-Step Instructions

1. Launch 3D Maps: Click any cell within your data table. Go to the Insert tab, and in the "Tours" section, click 3D Map.
2. Add Your Location Field: The 3D Maps window will open. On the right-hand side, a "Field List" will show the columns from your table. Drag your primary location field (e.g., "State") down into the Location box. Excel will try to identify the geography type (e.g., "State/Province"). If it guesses wrong, you can click the dropdown to correct it.
3. Add Your Value Field: Next, drag your numerical data field (e.g., "Revenue") into the Height box. You'll instantly see columns appear on the map, with the height of each column representing its value. Higher columns mean higher revenue!

Exploring 3D Map Customizations

This is where 3D Maps gets really fun. You aren't just limited to columns.

• Change the Visualization: At the top of the field panel, you can change the visualization from columns to a Heat Map (great for showing density) or Region (which creates a filled map, similar to our first method).
• Use the Category Field: You can add a text-based field (like a product line or campaign name) to the Category box. This will color-code your columns or heat maps based on that category, letting you visualize multiple variables at once.
• Add a Time Dimension: If your data includes dates, drag your date field to the Time box. This will add a time player to the bottom of your map, allowing you to play an animation showing how your data changes over days, months, or years.
• Customize the Look: In the top ribbon, you can add map labels, switch to a flat map view, and even apply different visual themes like 'vintage' or 'dark mode' for a more cinematic feel.

Best Practices for Effective Map Charts

Creating a map is one thing, creating an effective one is another. Here are a few final tips.

• Keep it Simple: Don't try to plot too many things at once. A map showing sales by state is clear. A map trying to show sales, profit, and units sold all at the same time will likely just be confusing.
• Use Color Meaningfully: Our brains are wired to associate darker colors with higher values. Stick to a simple, intuitive color gradient that naturally guides the viewer's eye.
• Add Context: Use clear chart titles (e.g., "Q4 Revenue by State" instead of just "Chart 1") and make sure your legend is easy to understand.

Final Thoughts

Plotting data on a map in Excel transforms rows of geographical data into a clear, intuitive visual story. By preparing your data correctly and choosing between the straightforward Filled Map or the dynamic 3D Map, you can uncover regional insights and present your findings in a way that everyone can immediately understand.

While Excel maps are great for ad-hoc analysis, the process still requires manual data prep and refreshing every time your numbers change. At Graphed , we created a way to get these insights without the manual wrangling. You can connect your live data sources (like Google Analytics, Shopify, or Salesforce) in just a few clicks and simply ask for what you need in plain English - say "create a map of website sessions by country for the last 90 days." Our AI builds a real-time, interactive dashboard for you instantly, so you can spend your time acting on insights rather than formatting cells.