Ozveri is wondering why the Excel Filled Map Chart shows the entire world when he has just a few countries around the Mediterranean Sea. It is an annoying part of Excel Filled Map Charts. But there is a solution. It is buried where you will never find it.
Format the data series in Excel and choose Only Regions with Data.
Also in this video, I show you how to create a tiny macro in your personal macro workbook that will quickly change this setting.
The macro from 3:00 minute mark:
Sub ShowOnlyRegionsWithData()
' ShowOnlyRegionsWithData Macro
ActiveChart.FullSeriesCollection(1).GeoMappingLevel = xlGeoMappingLevelDataOnly
ActiveChart.FullSeriesCollection(1).RegionLabelOption = xlRegionLabelOptionsShowAll
End Sub
Table of Contents
(0:00) Welcome
(0:20) Why World Map in Excel Map Chart when only a few countries?
(0:35) Create a Filled Map Chart in Excel
(0:50) Format Map Chart Only Regions with Data
(1:20) Compare Four Choices for Region to Show
(1:49) Both sides of US/Canada border
(2:06) Why Excel Map Chart defaults to Entire World?
(2:50) Create Personal Macro Workbook
(3:28) Pasting VBA Code in Excel VBA Editor
(3:54) Adding Icons to QAT in Excel
(4:42) Testing the Macro to format the Filled Map Chart
(5:00) Including Region without Data
(5:33) Even values of 0 have some color in the map
(5:47) Diverging Three Color Gradient for Map Chart in Excel
(6:20) Border between two missing states is missing
(6:42) Wrap up
(7:12) Nancy/Wally
(7:36) Outtake: Drawing the border between two zero regions
Format the data series in Excel and choose Only Regions with Data.
Also in this video, I show you how to create a tiny macro in your personal macro workbook that will quickly change this setting.
The macro from 3:00 minute mark:
Sub ShowOnlyRegionsWithData()
' ShowOnlyRegionsWithData Macro
ActiveChart.FullSeriesCollection(1).GeoMappingLevel = xlGeoMappingLevelDataOnly
ActiveChart.FullSeriesCollection(1).RegionLabelOption = xlRegionLabelOptionsShowAll
End Sub
Table of Contents
(0:00) Welcome
(0:20) Why World Map in Excel Map Chart when only a few countries?
(0:35) Create a Filled Map Chart in Excel
(0:50) Format Map Chart Only Regions with Data
(1:20) Compare Four Choices for Region to Show
(1:49) Both sides of US/Canada border
(2:06) Why Excel Map Chart defaults to Entire World?
(2:50) Create Personal Macro Workbook
(3:28) Pasting VBA Code in Excel VBA Editor
(3:54) Adding Icons to QAT in Excel
(4:42) Testing the Macro to format the Filled Map Chart
(5:00) Including Region without Data
(5:33) Even values of 0 have some color in the map
(5:47) Diverging Three Color Gradient for Map Chart in Excel
(6:20) Border between two missing states is missing
(6:42) Wrap up
(7:12) Nancy/Wally
(7:36) Outtake: Drawing the border between two zero regions
Transcript of the video:
I can solve this one in two clicks at the end. Creating a map chart in Excel.
And we only want to show the regions with data.
A few days ago I had this video about the Excel feedback and I went out to the feedback site just to see what people had posted so I could vote for all of your ideas, and I read this one, the maps chart.
"Even though I only have European countries, it displayed the whole world map.
It'd be nice to at least have a continent or region selected suggestions within the graphic styles". All right, so here's supposed to happen.
We choose this table, we go to insert filled map chart, and we get this view right here, which in my case is just giving me Europe, but I understand how that doesn't always work.
And the options that we have here, if we right click format data series and then come over to the panel on the right hand side...
First off, map labels. I go to show all every single time.
That doesn't mean they will show all, but they'll show more than best fit only.
And then here are the four choices under map area.
We have automatic, which is what we're getting there, only regions with data, ultimately this is my favorite, multiple countries or regions, and world.
So if they would show the world chart, it'd be even worse, right? I can't make out anything there.
In fact, here, let's just do this.
Here I have a comparison to the four different styles. Automatic.
So I have several states in the southeast of the United States.
Automatic gives me all the United States, only regions with data that zooms into just the areas that I have data for.
Country region is the same as automatic, so in other words, automatic is choosing country or region, and then world is just terrible.
All right?
And then I said, "Okay, let's try and break this". Let's go up to the Pacific Northwest.
We'll have a few US dates and throw in three Canadian provinces. And what happens?
So automatic now switches from United States to North America.
They throw Mexico in there.
Only regions with data to me is still the best one.
Country region is what we're getting with automatic, and world is still terrible.
All right, so the question is, the person who left the feedback, why are they getting the entire world?
And if the countries were just in Europe, it seems to work pretty well.
The issue is let's say it's a shipping company and they have some customers on the other side of the Mediterranean Sea.
So I throw in Morocco, Algeria, Tunisia, and Libya.
Well, now the automatic chart automatically defaults to the world chart, which again, I think is pretty much the worst.
And if we ask for country or region, they don't have anything that gives us just part of Europe and part of Africa, so we need to come into only regions with data.
All right, so here's the two click solution that we're going to have.
Just a few days ago, episode 2373, Excel Create A Personal Macro Workbook.
You want to go watch that if you've never used the personal macro workbook before.
And then I'll put this code down in the YouTube description.
You're going to copy that code, control C.
On the developer tab, you should have the developer tab, if you watched episode 2373, we're going to come out to the visual basic editor.
We're going to find the personal macro workbook and expand it, expand modules, and just click on any module that you have there. If you just did 2373, you'll have module one.
I think my last module was module six. Double click.
And we'll just paste the new code from the YouTube description here.
Show only regions with data, this does two things.
It takes the active map and it changes to the mapping level only regions with data, excel geo mapping level data only, just because I do it every single time. I change the region label options to show all.
Okay, so we have this little macro here, show only regions with data.
All right, so here's what I think you should do if you're creating a lot of map charts.
Come here to insert, maps, right click, and say add gallery to quick access toolbar.
All right? So right there, that's the map chart icon.
And then we're going to right click and say customize quick access toolbar.
From the left dropdown, choose macros.
Find show only regions with data. Click add.
I don't like that icon, but there's not a lot of better icons here, unfortunately.
I don't care. Choose anything.
And then give it a nice little tool tip here, like fix map chart.
Click okay. All right, there we go.
Click okay. All right, so now we have two icons.
This one creates the map chart, and then this one will fix the map chart.
Let's try it.
Here's our list of countries that include Europe and Africa. We'll come up here, we'll hit the map chart icon.
It's annoying. It's a gallery, right?
Choose that, and then right next to it run the macro to say only regions with data.
I think that's probably the best way to go. Now, one last thing here.
And this is the one that's going to drive me crazy is when you get with only regions with data, if you have a region, or in this case a state that doesn't have any data, it just shows up as a white gap.
And I'd really love to have the outline there and maybe the state names appear, I guess.
All right, so what I'm going to do is I'm going to add these two items to the map, Alabama with sales of zero and Mississippi. All right.
See, but they still show up in a very, very light blue, which is annoying.
So right click, format data series.
And then over here on the far right hand side you have the bar chart selected.
We're going to go to series color and we're going to change this to be a diverging three color.
Diverging three color.
Let's make this wider so we can see all the options out here.
For the minimum, I'm going to say I want a specific number. For the middle value, I'm going to say a number.
And what I'm going to do is I want those zeros to show up in white, so I choose a white and say anything less than two, let's say, and then the midpoint starts at three.
And so now I'm going to have to play with these colors a little bit.
But you see I now have Mississippi and Alabama showing up with a label, still no line between them, but they're not showing up there with data, which is what I was trying to avoid.
So we'll go with a light blue here and a dark blue.
And we get that look of the original map chart, but get the labels on the missing states without having any extra color. All right.
So hey, thanks to everyone who watched 2366 and posted a new idea at the feedback channel.
I voted for all of your ideas, at least all of them that have happened since this video came out.
So thanks for that.
Thanks to Ozveri for this idea about mapping just the European countries.
I can see how frustrating it would be if you have some countries in Europe and some countries just outside of Europe.
But hopefully this short little macro on the personal macro workbook with an icon up there for map chart and then fix the map chart will make life easier.
All right. Hey, I want to thank everyone for stopping by.
We'll see you next time for another net cast from MrExcel.
Just in case you're wondering who this guy is, this is Wally Faresten.
He does the cue cards for Saturday Night Live for the last 30 years, and Late Night with Seth Myers.
I try not to nag you about this, but Wally created the card.
So let me ask you to please like, subscribe, and ring the bell.
Oh, no. Yep.
We can get the boarders around the zero states.
So first click on the chart, click on the state you want, and then instead of the three columns, come here to the paint bucket under border, change to a solid line, click on the next state solid line, and they'll draw the border in between the states.
And we only want to show the regions with data.
A few days ago I had this video about the Excel feedback and I went out to the feedback site just to see what people had posted so I could vote for all of your ideas, and I read this one, the maps chart.
"Even though I only have European countries, it displayed the whole world map.
It'd be nice to at least have a continent or region selected suggestions within the graphic styles". All right, so here's supposed to happen.
We choose this table, we go to insert filled map chart, and we get this view right here, which in my case is just giving me Europe, but I understand how that doesn't always work.
And the options that we have here, if we right click format data series and then come over to the panel on the right hand side...
First off, map labels. I go to show all every single time.
That doesn't mean they will show all, but they'll show more than best fit only.
And then here are the four choices under map area.
We have automatic, which is what we're getting there, only regions with data, ultimately this is my favorite, multiple countries or regions, and world.
So if they would show the world chart, it'd be even worse, right? I can't make out anything there.
In fact, here, let's just do this.
Here I have a comparison to the four different styles. Automatic.
So I have several states in the southeast of the United States.
Automatic gives me all the United States, only regions with data that zooms into just the areas that I have data for.
Country region is the same as automatic, so in other words, automatic is choosing country or region, and then world is just terrible.
All right?
And then I said, "Okay, let's try and break this". Let's go up to the Pacific Northwest.
We'll have a few US dates and throw in three Canadian provinces. And what happens?
So automatic now switches from United States to North America.
They throw Mexico in there.
Only regions with data to me is still the best one.
Country region is what we're getting with automatic, and world is still terrible.
All right, so the question is, the person who left the feedback, why are they getting the entire world?
And if the countries were just in Europe, it seems to work pretty well.
The issue is let's say it's a shipping company and they have some customers on the other side of the Mediterranean Sea.
So I throw in Morocco, Algeria, Tunisia, and Libya.
Well, now the automatic chart automatically defaults to the world chart, which again, I think is pretty much the worst.
And if we ask for country or region, they don't have anything that gives us just part of Europe and part of Africa, so we need to come into only regions with data.
All right, so here's the two click solution that we're going to have.
Just a few days ago, episode 2373, Excel Create A Personal Macro Workbook.
You want to go watch that if you've never used the personal macro workbook before.
And then I'll put this code down in the YouTube description.
You're going to copy that code, control C.
On the developer tab, you should have the developer tab, if you watched episode 2373, we're going to come out to the visual basic editor.
We're going to find the personal macro workbook and expand it, expand modules, and just click on any module that you have there. If you just did 2373, you'll have module one.
I think my last module was module six. Double click.
And we'll just paste the new code from the YouTube description here.
Show only regions with data, this does two things.
It takes the active map and it changes to the mapping level only regions with data, excel geo mapping level data only, just because I do it every single time. I change the region label options to show all.
Okay, so we have this little macro here, show only regions with data.
All right, so here's what I think you should do if you're creating a lot of map charts.
Come here to insert, maps, right click, and say add gallery to quick access toolbar.
All right? So right there, that's the map chart icon.
And then we're going to right click and say customize quick access toolbar.
From the left dropdown, choose macros.
Find show only regions with data. Click add.
I don't like that icon, but there's not a lot of better icons here, unfortunately.
I don't care. Choose anything.
And then give it a nice little tool tip here, like fix map chart.
Click okay. All right, there we go.
Click okay. All right, so now we have two icons.
This one creates the map chart, and then this one will fix the map chart.
Let's try it.
Here's our list of countries that include Europe and Africa. We'll come up here, we'll hit the map chart icon.
It's annoying. It's a gallery, right?
Choose that, and then right next to it run the macro to say only regions with data.
I think that's probably the best way to go. Now, one last thing here.
And this is the one that's going to drive me crazy is when you get with only regions with data, if you have a region, or in this case a state that doesn't have any data, it just shows up as a white gap.
And I'd really love to have the outline there and maybe the state names appear, I guess.
All right, so what I'm going to do is I'm going to add these two items to the map, Alabama with sales of zero and Mississippi. All right.
See, but they still show up in a very, very light blue, which is annoying.
So right click, format data series.
And then over here on the far right hand side you have the bar chart selected.
We're going to go to series color and we're going to change this to be a diverging three color.
Diverging three color.
Let's make this wider so we can see all the options out here.
For the minimum, I'm going to say I want a specific number. For the middle value, I'm going to say a number.
And what I'm going to do is I want those zeros to show up in white, so I choose a white and say anything less than two, let's say, and then the midpoint starts at three.
And so now I'm going to have to play with these colors a little bit.
But you see I now have Mississippi and Alabama showing up with a label, still no line between them, but they're not showing up there with data, which is what I was trying to avoid.
So we'll go with a light blue here and a dark blue.
And we get that look of the original map chart, but get the labels on the missing states without having any extra color. All right.
So hey, thanks to everyone who watched 2366 and posted a new idea at the feedback channel.
I voted for all of your ideas, at least all of them that have happened since this video came out.
So thanks for that.
Thanks to Ozveri for this idea about mapping just the European countries.
I can see how frustrating it would be if you have some countries in Europe and some countries just outside of Europe.
But hopefully this short little macro on the personal macro workbook with an icon up there for map chart and then fix the map chart will make life easier.
All right. Hey, I want to thank everyone for stopping by.
We'll see you next time for another net cast from MrExcel.
Just in case you're wondering who this guy is, this is Wally Faresten.
He does the cue cards for Saturday Night Live for the last 30 years, and Late Night with Seth Myers.
I try not to nag you about this, but Wally created the card.
So let me ask you to please like, subscribe, and ring the bell.
Oh, no. Yep.
We can get the boarders around the zero states.
So first click on the chart, click on the state you want, and then instead of the three columns, come here to the paint bucket under border, change to a solid line, click on the next state solid line, and they'll draw the border in between the states.
