Excel Map Chart - Only Regions With Data And Macro

50 Aufrufe

Danke für die Bewertung! Teile es mit Deinen Freunden!

Danke für Deine Bewertung!

Published Feb 27, 2023

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