All language subtitles for 4. Using Map Charts

In the last lesson, we were taking a look at formatting pivot charts in Excel.

And since then, I've just gone in and I've added a bit more formatting to these charts, so I've added

a border to these balls.

And I've also added an icon and also a little shape just to add a little bit of interest and personality.

And if you're looking for these two items, you'll find them on the insert ribbon under shapes and also

under icons.

If we jump across and take a look at our pie charts again, I've just changed the colors that I'm using.

I've added some borderlines and I've made the pie chart a little bit bigger.

So what we're going to do in this lesson is we're going to take a look at a different type of chart

and that is a map charts.

Now, map charts are perfect for visualizing geographical based data.

And the reason why I want to show you map charts is because they do work slightly differently to any

other chart that you'll create in Excel.

So the first thing I'm going to do here is I'm going to take another copy of this worksheet, so hold

down control and just drag and drop the tab to create a copy.

Now, because map charts utilize geographical data.

I'm going to click in my pivot table and instead of displaying the products, we're going to display

the countries.

Let's grab that and drag it down to rows.

And I'm not going to do a count of the product this time.

We're going to do the gross sales again.

Now we don't need this pie chart, so let's just delete that out.

Now, if we click inside our pivot table and then jump up to Pivot Table, Analyze and pivot chart.

Notice here we have a map chart option, but check out what's happening.

It says you can't create this chart type with data inside a pivot table.

So that is the first issue you're going to come across if you want to create a nice field map chart.

You cannot create it if your data is coming from a pivot table.

So how can we do this?

Well, the answer here is simply to remove the data from the pivot chart.

So I'm going to select all of this data control C to copy.

And I'm just going to paste it underneath, but I'm going to paste it without any formatting.

So we're just going to paste the values.

So I still have my column headings.

Just here are my data, but it's no longer inside a pivot table.

How can I tell it's not inside a pivot table?

Well, when I click in the data, I no longer get any contextual ribbons related to pivot tables.

If I click in the one above, I get back my pivot table, analyze and design ribbons.

So now this data is no longer inside a pivot table.

I can create a map chart.

So let's select the data.

And this time we want to go to insert and then in charts, we can select maps, so let's select filled

map.

And there we go.

If we drag this out, you can see it's basically showing a map of the world and then those five countries

are shaded, and that is based on the sum of gross sales.

So the higher the gross sales, the darker the color, the lower the lighter the color.

And you could do a map chart, not just specifically for countries.

If you have maybe states of America listed out and some figures, you can create a map chart for that

as well.

And you won't see the entire world.

You'll just see the United States.

Now, the disadvantage of doing it this way by removing the data from the pivot table is that this isn't

particularly dynamic.

So if any of the figures change in the pivot table, my match isn't going to update.

So once we've taken this data out of this pivot table and we've created our chart, what we then need

to do is point this chart back to the pivot table data so that if any changes are made, the charts

are going to update.

So this is fairly straightforward.

We can click on the charts, we can go up to chart design and let's go to select data.

So now, instead of using this data range to populate our charts, we can simply delete that now and

just select the pivot table data.

Let's click on OK.

Nothing changes, but we're now using that pivot data in our charts.

So it's a bit of a strange one because you can't create a chart based on pivot table data, so you have

to remove it, create the chart and then point the chart back to the pivot table.

Once I've done that, I can simply delete out that secondary data.

And of course, then we can go in and we can start formatting this chart using all of our usual options.

So I'm going to do a couple of changes here.

Let's go to chart design and we have some chart styles.

If I hover over, I can see the way that that looks.

So I think I'm going to go for this one that just here, I'm going to change the colors.

Let's stick with a kind of green color scheme that we have going on I like, so I'm going to give my

chart a little title.

So this is gross sales by country, and I maybe want to move that legend to.

I want this on the left.

Yes, I think why not?

And of course, you could carry on going editing this job, but that's how you can create a map chart

based off of pivot table data.