How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps

(Note: This tutorial is suitable for Excel 2019, 2021 and Microsoft 365 versions.)  

In this guide, let us see how to create an Excel map chart using Pivot Table data to present geographical information. 

You’ll learn:

• Map Chart in Excel – An Introduction
• How to Create an Excel Map Chart?
• How to Format an Excel Map Chart?

Map Chart in Excel – An Introduction

Map Charts in Excel display geographical data. We use them when we have data that contains countries, regions, states, counties or zip codes. The data is presented on a map with each region shaded to reflect the value. This produces a very effective and eye-catching result. 

Map Charts can easily be created from Excel table data but it’s traditionally been impossible to create them using Pivot Table data.

In the screenshot below, we tried to insert a map chart based on the PivotTable data. However, we can see that we are unable to create this type of chart inside a Pivot Table. 

It is possible to create a map chart that uses Pivot Table data, we just need to use a simple work-around. 

The same workaround needs to be employed if trying to create many of the newer chart types: Sunburst, Treemap, Waterfall, Box and Whisker, Histogram and Funnel charts using PivotTable data. 

Related:

How to Create an Excel Heat Map? 5 Simple Steps

How to Custom Sort Excel Data? 2 Easy Steps

Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP

How to Create an Excel Map Chart?

First, we need to ensure that the data we want to use in the map chart is appropriate. Map charts only work with geographical data so we must have a field that contains countries, regions, zip codes, states etc. in the PivotTable. 

If we try to create a map chart directly from our Pivot Table, we will receive a message letting us know we cannot create this chart type using data inside a Pivot Table. 

Step 1: Copy the Pivot table data

The solution is to remove the data from Pivot Table first and then create the map chart. 

• Click in the PivotTable and press Ctrl+A to select all the data. 
• Click in a blank cell somewhere else in the worksheet. 
• From the Home tab, in the Clipboard group, click the lower-half of the Paste button. 
• Click Paste Values. 

This removes the data from the PivotTable and pastes it as plain text without any formatting. We can use this data to create the map chart. 

Step 2: Create a cap chart using the copied data

• Click inside the new data. 
• From the Insert tab, in the Charts group, click Maps. 
• Select Filled Map.  

Each country that’s included in the dataset is represented on the map and shaded according to its value. In this example, the higher the number of units sold, the darker the blue shading. 

This looks good; however, we have a problem. If the PivotTable data changes, the map chart will not update as it is not using the PivotTable data to populate the values. We need to modify the chart and point it back to the PivotTable data. 

Step 3: Link the map chart to the pivot table

• Click on the chart. 
• From the Chart Design tab, in the Data group, click Select Data. 
• In the Select Data Source dialog box, click the up arrow next to Chart data range. 
• Select the PivotTable data. 

• Click OK. 

The map chart is now using the PivotTable data so we can delete the copied data. 

• Click inside the copied data and press CTRL+A to select all. 
• Press the Delete key. 

Try making a change to the PivotTable to make sure the map chart updates. Here, I have applied a Top 10 Filter to the PivotTable to only show the top 50 countries by the sum of units sold. 

Suggested Reads:

How to Print Gridlines in Excel?

Start Co-Authoring Excel Workbooks in 6 Easy Steps

How to Insert a Page Break in Excel? (3 Simple Steps)

How to Format an Excel Map Chart?

We can apply formatting to our map chart using the Chart Design and Format ribbons. These are contextual ribbons that only display when we are clicked on the chart. 

• From the Chart Design tab, in the Chart Styles group, select Style 2. 
• From the Chart Design tab, in the Chart Styles group, click Change Colors. 
• Choose Colorful Palette 3 from the gallery. 

• Click the + next to the chart. 
• Hover the mouse over Legend. 
• Choose Right from the menu. 

• Double-click on the Chart Title. 
• Change the title to ‘Sum of Units Sold by Country’. 

More formatting options can be found in the right-click menu. 

• Click on the part of the chart to format.  
• Right-click and select Format Data Series from the menu. 

From the Format Data Series pane, we have access to many more formatting options. 

The options we see in this pane change depending on which part of the chart we select, e.g., if we click on the ‘Chart Title’ we will see formatting options related to the title. 

Also Read:

Arrow Keys Not Working in Excel – 4 Easy Fixes

How to Record a Macro in Excel? In 6 Easy Steps (For Dummies)

6 Easy Methods to Strikethrough in Excel

Closing Thoughts

In this guide, we saw how to create an Excel map chart using pivot tables. I recommend you practice these steps in a sample worksheet to gain a better understanding.

Visit our free Excel Resources Centre for more high-quality Excel guides. 

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. 

You can train your entire team in Excel and other business software, for a single low monthly fee by clicking here.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.