How to Make a Map Chart in Excel by Country or State
By ExcelReady TeamUpdated 6 min read
The short answer
To make a map chart in Excel, put one location per row in the first column, such as a country or state, and one value in the next column. Select both columns and choose Insert > Maps > Filled Map. Total raw data by region first with SUMIF. Map charts need Excel 2019, Excel 2021 or later, or Microsoft 365.
Key takeaways
- A map chart needs a summary table: one row per country, state or region, and one value per row.
- Insert it with Insert > Maps > Filled Map; Excel matches the place names online through Bing.
- Total transaction-level data by region with SUMIF or SUMIFS before you chart it.
- Add a Country column next to states or cities that share a name with another place.
- Map charts shade whole regions; they can't plot street addresses or latitude and longitude points.
On this page+
A map chart in Excel shades each country, state or region by its value, so you can see at a glance where sales, customers or survey responses are highest. You build it from a small table with one place per row and one number per row, then choose Insert > Maps > Filled Map. This guide shows how to prepare the data with SUMIF, create the chart, format it and fix the common errors.
What you need before you make a map chart#
You need a version of Excel that has map charts, an internet connection, and a summary table.
- Version: filled map charts are in Excel 2019, Excel 2021, Excel 2024 and Microsoft 365, on Windows and Mac. Older one-time-purchase versions, such as Excel 2016, don't have them.
- Internet: when you create the chart, Excel sends the place names to the Bing service to match them to map shapes.
- Data shape: one column of locations and one column of values. Each location appears once.
The values can be numbers, which give a colour scale from light to dark, or text categories, which give one colour per category. Map charts shade whole areas: countries, states or provinces, counties and postal codes. They can't plot points from street addresses or from latitude and longitude.
How to total your data by state with SUMIF#
Most real data is a list of transactions, with the same state on many rows. A map chart needs one total per state, so summarise first. Here is an order list:
| A | B | C | |
|---|---|---|---|
| 1 | Order | State | Sales |
| 2 | SO-101 | Texas | 1,200 |
| 3 | SO-102 | Ohio | 800 |
| 4 | SO-103 | Texas | 950 |
| 5 | SO-104 | Florida | 1,500 |
| 6 | SO-105 | Ohio | 400 |
| 7 | SO-106 | Texas | 600 |
| 8 | SO-107 | Florida | 700 |
Type the state names in E2:E4 (Texas, Ohio, Florida). In F2, type:
=SUMIF($B$2:$B$8,E2,$C$2:$C$8)Fill down to F4. You get 2,750 for Texas, 1,200 for Ohio and 2,200 for Florida. The $ signs keep the ranges fixed as the formula moves. The SUMIF function guide explains each argument.
In Microsoft 365 or Excel 2021, you can make the state list with a formula too: =SORT(UNIQUE(B2:B8)). This is the same pattern as summing by category.
Totals with two conditions#
To map one year or one product only, use SUMIFS, which takes the range to add first and then pairs of conditions:
=SUMIFS($C$2:$C$100,$B$2:$B$100,E2,$D$2:$D$100,2025)This adds sales for the state in E2 where column D holds 2025. The SUMIFS function guide covers more criteria.
How to insert a filled map chart#
Once the summary table is ready, the chart takes a few clicks:
- Select the summary table, including the headers: E1:F4.
- Go to Insert > Charts > Maps and choose Filled Map.
- Wait a moment while Excel matches the place names. The map appears, zoomed to the area that has data.
- Drag the chart where you want it and resize it from a corner.
If Excel can't match some names, it shows a note on the chart that some data could not be mapped, and those places stay blank. Fix the names in the table, and the chart updates.
The steps for general chart editing, such as titles and legends, are the same as for any chart. See Excel charts basics for a refresher.
How to format a map chart#
Double-click a shaded area to open the Format Data Series pane. The Series Options section has the map-specific settings:
| Setting | What it does |
|---|---|
| Map projection | Changes the map's shape, such as Mercator or Miller |
| Map area | Shows only regions with data, or the whole world |
| Map labels | Shows region names on the map: none, best fit or all |
| Series Color | Sets a 2-color sequential scale or a 3-color diverging scale |
Use a sequential scale (light to dark) for values like sales, where higher simply means more. Use a diverging scale, with a midpoint colour, for values that can go above or below a target, like growth rates. Change the chart title from the Chart Design tab or by clicking the title.
How to fix wrong or missing regions#
Wrong regions almost always come from place names Excel can read in more than one way. Try these fixes:
- Add a Country column. "Georgia" is both a US state and a country. Put Country in the column before State, select both columns plus the values, and recreate the chart.
- Use full official names. "Calif." or "N. Carolina" may not match. Use full names such as "California" and "North Carolina".
- Remove extra spaces. "Texas " with a trailing space may fail. Clean the column with
=TRIM(B2). - Remove subtotal and total rows. A row named "Total" will not match a region and can distort the colour scale.
- Keep one level of detail. Mixing countries and states in one chart confuses the matching. Make one chart per level.
Map chart vs bar chart: which should you use?#
Use a map chart when where matters, such as spotting regional patterns or gaps in coverage. Use a bar chart when the exact ranking or comparison matters, because lengths are easier to compare than shades of colour.
Small regions are hard to see on a map, and a large region with a small value can still draw the eye. If your audience needs exact numbers, add data labels or show a bar chart sorted by value next to the map. A pie chart is rarely a good choice for many regions, because slices become too thin to read.
Can you use a pivot table instead of SUMIF?#
Yes for the summary, but not as the chart's direct source. A PivotTable is a fast way to total sales by state; see the beginner's guide to pivot tables. However, Excel can't build a map chart from a PivotTable. Copy the pivot table, paste it as values in a new range, and chart that range. The downside is that pasted values don't update when the data changes.
SUMIF formulas do update. When a new order is added inside the ranges, the totals and the map change straight away. That makes formulas the better choice for a map you will reuse each month.
Practise it#
A good map chart starts with a clean summary table. Practise SUMIF in a live spreadsheet to total values by region, then build a pivot-style summary with formulas using UNIQUE, SORT and SUMIFS. You type each formula and it checks it instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Before you can map sales by state, you need one total per state. In H2, use SUMIF to total the Sales (C2:C8) for Texas in the State column (B2:B8).
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Sales | ||||||||
| 2 | Texas sales | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 |
Tip: SUMIF takes the column to check (B2:B8), what to match ("Texas") and the column to add (C2:C8).
Practise this in a lesson
Frequently asked questions
Why don't I have the Maps chart option in Excel?
Filled map charts are in Excel 2019, Excel 2021, Excel 2024 and Microsoft 365. Older one-time-purchase versions such as Excel 2016 don't have them. Creating a new map chart also needs an internet connection.
Can Excel make a map chart by city or zip code?
Excel's filled map shades areas such as countries, states, counties and postal codes. It can't place a dot on a city or address. For cities, chart them by their state or county instead, or use a different tool.
Why does my Excel map chart show the wrong country?
The place name is ambiguous, such as Georgia (a US state and a country). Add a column with the country name next to the state column and include both columns when you create the chart.
Can I make a map chart with text categories instead of numbers?
Yes. If the value column holds text, such as sales region names, Excel colours each region by category instead of a colour scale, and adds a legend for the categories.
Can I use a pivot table for a map chart?
Not directly: a map chart can't be built from a PivotTable. Copy the pivot table and paste it as values, or build the summary table with SUMIF formulas, and chart that range.