Locked lesson.

Upgrade

Quick reference

Create a Map Chart

Create a Filled Map Chart using PivotTable data. 

When to use

Map Charts are a great choice if we have geographic data that we need to visualize. 

Instructions

Map Charts help us visualize geographic data. However, if we want to create a map chart using data that's in a PivotTable we are going to run into some issues. Map Charts (and other chart types in Excel) cannot be directly created from PivotTable data. 

In the screenshot below, we have a PivotTable. If we try and create a Map Chart, we get an error. 

We can work around this by removing the data from the PivotTable first, creating the map chart and then pointing the chart back at the PivotTable data. 

  • Click anywhere in the PivotTable. 
  • Press CTRL+A to select all. 
  • Press CTRL+C to copy 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 will paste the values only without the PivotTable. 

  • Click anywhere in the new table of data. 
  • From the Insert tab, in the Charts group, click Maps and Filled Map. 

The map chart will be inserted into the worksheet. 

If we click on the chart, we can see the dataset that the chart is using.

If anything updates in the source data, the chart will not update because it's not using the PivotTable data. 

  • Click on the chart. 
  • From the Chart Design tab, in the Data group, click Select Data. 
  • In the Chart data range field, replace the current cell range with the PivotTable cell range. 
  • Click OK. 

 

  • Delete the second table. 

Hints & tips

  • This process can be followed for any chart type that cannot be built directly using PivotTable data. 
Login to download
  • 00:04 In the previous lesson,
  • 00:05 we started to talk about how we can use charts to visualize our pivot table data.
  • 00:10 And one of the things I highlighted was that we
  • 00:13 cannot create all types of chart based off of pivot table data.
  • 00:17 For example, if I click in my data just here, jump up to PivotTable Analyze and
  • 00:22 Pivot Chart, if I click on Map chart, for example, check out what we have a little
  • 00:27 message that says you can't create this chart type with data inside a pivot table.
  • 00:33 Now, what if I want to use this pivot table data to create a map chart?
  • 00:38 How can we get around this?
  • 00:39 Well, it's actually quite simple.
  • 00:41 So let me show you how.
  • 00:43 Now, first off, I have a small pivot table just here,
  • 00:46 which is showing the sum of total profit for basically the North America region,
  • 00:52 Canada, Mexico, and the United States.
  • 00:55 And this pivot table is just using the country field and the total profit field
  • 01:00 and I've just simply applied a filter to only show Canada, Mexico, and the US.
  • 01:05 And what I want to do is I want to create a nice map chart that shows these
  • 01:10 figures as shading on a map.
  • 01:11 Now as we've already seen, we can't simply jump up to Pivot Chart, select Map,
  • 01:16 because it doesn't let us create it.
  • 01:18 So what we need to do here is we need to select our pivot table, Ctrl-C to copy.
  • 01:25 And I'm going to paste it somewhere down here, but we're going to go to our paste
  • 01:30 options, and we're going to choose to Paste the Values only.
  • 01:34 Now, what that effectively does is it removes that data from the pivot table.
  • 01:39 So we can now click in our data, and just insert a regular map chart.
  • 01:44 So we can jump to the insert ribbon.
  • 01:46 And then in the chance group, notice we have a little maps option just here.
  • 01:52 So I'm going to select Maps and we're going to choose Filled Map.
  • 01:56 And check out what we get, we get a map of North America,
  • 02:00 you can see the sum of total profit is represented by a gradient fill color.
  • 02:06 The higher the total profit, the darker the blue, the lower, the lighter the blue.
  • 02:11 Now of course, we can go in and we can start formatting this chart.
  • 02:14 We can maybe do things like change the colors if we want this to be green.
  • 02:18 We could even choose a different chart style if we wanted to make that
  • 02:22 really funky.
  • 02:22 We can add a chart title, all of that good kind of stuff.
  • 02:26 Now the problem that we have building this chart this way is that this chart is
  • 02:30 using this data here that isn't inside the pivot table.
  • 02:34 So what it basically means is if any of the source data changes,
  • 02:38 the chart isn't going to update.
  • 02:41 Because the data that it's using is not linked to the source data because
  • 02:46 it's not in a pivot table.
  • 02:48 So how can we get around this?
  • 02:50 Well, it's pretty straightforward.
  • 02:51 We just need to click on the charts, jump up to Chart Design, and go to Select Data.
  • 02:58 And what we can effectively do here is point the map chart back to
  • 03:02 the original pivot table data.
  • 03:05 So you can see at the top here the range of cells that it's using for
  • 03:09 this map chart, so we're on the pivot tab using cell range A12 to B15.
  • 03:15 So all we need to do here is delete that out and
  • 03:18 point it back at the pivot table data.
  • 03:21 When we click on OK, nothing changes in the map chart,
  • 03:24 which is perfect, and we can then go in and delete out that other data.
  • 03:29 So what this means is that if anything updates in this source data file, once we
  • 03:34 right-click and refresh, everything is going to update nicely in the map chart.
  • 03:39 So that is how you can actually use pivot table data to
  • 03:44 build some of these other chart types like histograms,
  • 03:50 box and whisker charts, map charts like we've done here, so on and so forth.

Lesson notes are only available for subscribers.

Gift this course