Excel Map Chart - Only Show Regions With Data And Macro - Episode 2555

Microsoft Excel Tutorial: Map Only Regions With Data in Excel.

Welcome to another Excel tutorial from MrExcel! In this video, we will be discussing how to create a map chart in Excel and how to only show regions with data. This topic was suggested by one of our viewers on the Excel feedback site, and we are excited to share this solution with you.

If you have ever tried to create a map chart in Excel, you may have noticed that it can be frustrating when the entire world map is displayed, even if you only have data for a specific region. This can make the chart difficult to read and understand. However, with just two clicks, we can solve this issue and only display the regions with data.

In this video, we will walk you through the steps to create a map chart and then show you how to use a simple macro to only display the regions with data. We will also discuss the different options for map labels and map areas, and explain why the "only regions with data" option is our favorite. Additionally, we will show you how to customize your quick access toolbar to make this process even easier in the future.

But that's not all! We will also address the issue of missing borders and labels for regions with no data. With a few simple adjustments, we will show you how to add these elements to your map chart without cluttering it with unnecessary colors. And as always, we will provide you with the code for the macro used in this tutorial in the YouTube description.

We want to thank our viewer Ozveri for suggesting this topic and for all of you who have left feedback on the Excel feedback site. We appreciate your ideas and suggestions, and we always try to incorporate them into our tutorials. So don't forget to like, subscribe, and ring the bell to stay updated on our latest videos. And if you want to learn more about Excel, be sure to check out our other tutorials and net casts. Thank you for watching and we'll see you next time for another Excel tutorial from MrExcel.

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) Clicking Like really helps the algorithm
(7:36) Outtake: Drawing the border between two zero regions

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excel3dmap #excelmaps

This video answers these common search terms:
excel how to control map charts in excel
how to format map chart in excel
how to add map chart to excel 2016
how to add a map chart in excel
how to make a regional map in excel
how to fill a map chart excel
how to create a map chart in excel
how to create an excel map chart?

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

[leopickard]

Thank you Mr excel, I have come across this with maps charts recently too and for European countries! I gave up trying to fix it but now I know how!

[frichk]

Life saver, thank you! Somehow for me automatic defaulted to world map, but thanks to you I was able to change it!

[excelisfun]

So Simple : ) Thanks for the cool detail, Mr Excel!!!

[Candie]

Thanks for this. Its really helpful for uni work.

[dgodzisz]

@MrXL Thank you for this, very helpful. I need help with data labels on this. I'm showing data by state for all of the US and the data isn't visible in the New England states because of their small size. I cannot figure out how to move or resize just one data label like I can do in other Excel charts. Is this possible?

[KnowledgeMass]

Amazing thank you for the tip so it only shows me Europe, I had only European countries on and it showed me the world map at first which I didn't like, thank you for the tips on how to fix it much appreciated

[TravisKinley]

My Excel only shows 3 options under Format Data Series. The one I'm missing is, "Multiple Countries/Regions". I just checked for updates. Is it because I'm using Microsoft 365? The Microsoft website states my subscription gives me access to full versions of the software though? Have an idea why this may be?

[ppkmmm]

Thank you for your sharing! I have one question though, is the map filled chart can work with non-map related custom x, y coordinates with certain size in a table that reports the sensor value?
I noticed 3d map can do this up to specific point coordinates, but not custom shape area, and as for map filled chart, I am not exactly sure how to map them.
Example:
I have a floor plan image, and a table as below which the coordinates refers to a reference point for a building sensors mapping
Area, X coordinate, Y coordinate, Length (m), Width (m), sensor reading
Area A, 1.5m, 3.0m, 0.5m, 0.2m, 25 °C
Area B, 2.0m, 5.0m, ....
and so on

[ericknkonya]

Thank you this is very helpful. I still have another challenge I am facing. What if I want to just map data in lets say alabama state? or just want to do the same thing in Italy as a country only for the regions in the country? I can do that under the 3D map but I can't somehow do the same on just the map feature on excel, thoughts?

[peckham]

i have a right task for you that i have data for.. basically i have want to create a grid of lands per say
each one (1 x1, 2x 2, 3x3).. i need to have them form like states and countries.. over a many many thousand Grid Oblong (world map)
i note how you took different countries and populated your own map

can you or I have a file where i can insert the data that i have created for different lands, areas and a variety of different resources (for each of said 1 x 1 lands etc to then show over a wider view of the all the world possible.. something a kin to what you where making at the end where you bring in more countries on a map and make your own. (but i just want a 1x1 size)
how would you go about making that populate.

[BlackmanAlex]

I’m doing this with zip codes, is there a way to only show specific states

[lameloser]

I want to ask for help, but I don't even know how to ask what I want 😭