Reading Time: minutes remaining

How to Produce Amazing Map Charts in Excel

Jason Morrell

by Jason Morrell 
April 12, 2020

Select Dynamic field

If you've never tried Map Charts, give this a go.

Map charts work with geographical data, which can be country, regional or postcode specific. It then draws the appropriate map for you and divides it up (in pie chart fashion) into different sized segments based on your data and colour-codes each segment.

Consider this Australian population data:

How to assemble your data for a Map Chart in Excel

How to create a map chart

To create a map chart:

1.  Select a single cell in your data to select the entire range or select the specific data range you wish to use.

2.  Click the Insert tab then the Maps icon in the Charts group, or …

Click the Recommended Charts icon | All Charts tab | Map

How to create a Map Chart in Excel

3.  Select Filled Map.

  • The map is drawn for you. 
  • Each area is sized and colour-coded on the map. This is also reflected in the legend which displays a single colour gradient bar that includes all the colours on the map
Excel draws the Map Chart for you, complete with colour code and data

It is always worth checking out the Chart Styles on the Chart Tools Design ribbon for different configurations.

Map Charts can easily be enhanced by choosing a Chart Style from the Chart Tools ribbon

How to change the design layout of a map chart

To change the design layout of the map:

1.  Double-click anywhere on the chart to display a sidebar containing all appropriate options for your map.

2.  Click the drop-down arrow at the top and choose Series xyz, where xyz is your data (in this example that’s Population).

3.  Under Series Options you can change the Map Projection, Map Area and Map Labels.

  • Map Projection can be set to Automatic, Mercator, Miller and Albers.
  • Map Area gives you different region tags: Automatic, Only regions with data, Country/region, Multiple countries/regions and World.
  • Map Label can be set to None, Best Fit Only or Show All.
Under Series Options you can change the Map Projection, Map Area and Map Labels for the Map Chart

4.  Under Series Color you can choose your colours and gradients to be used on your map.

How to change the data label properties

To change the data label properties:

1.  Click the PLUS icon in chart icon controls on the top-right edge of the chart (or click Add Chart Element on the left of the Chart Tools Design ribbon)

2.  Click the Data Labels option arrow, then choose More Data Label Options.

3.  Tick the Category Name and Value boxes.

4.  Click the Number option arrow and choose a standard format or set a custom format

With a few tweaks, the map is better than it started, although it still has some obvious flaws such as

  • Victoria and Tasmania have incomplete labels and data..
  • The Australian Capital Territory (ACT) doesn't  even appear.

    For international readers the ACT is the territory where the Capital Federal Government is based. There's not much in the ACT besides Canberra, and there's not much in Canberra! As the farmers lamented when Canberra was first built: "Damn waste of a good sheep paddock!"
The Data labels on your Map Chart can be changed to enhance the chart even further

In the above example, the population figures have been shortened (e.g. 7.9m instead of 7,861,100) by formatting the data labels and using a custom number format [>=1000000] 0.0,,\m;[>=1000] 0,k;0i

Wrap

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

POPULAR EXCEL POSTS

Radar charts in Excel


Sunburst charts in Excel


Box plots (Box & Whisker charts) in Excel


Waterfall charts in Excel


Funnel charts in Excel


VLOOKUP de-mystified


How to fix broken formulas fast


3 Ways To Insert a Degree Symbol In Excel


11 little-known sorting secrets (wait until you see 5, 6 and 9)

«« SEE ALL EXCEL POSTS »»

more from

Office Mastery logo
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>