• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Map Charts

You are here: Home / Excel Charts / Excel Map Charts
March 16, 2017 by Mynda Treacy

New in Excel 2016 (for Office 365 subscription users only*) are Map Charts.

Office 365 map charts

*If you have Office 365 and can’t see the Map Charts then it’s likely you’re on the Deferred Channel, which hasn’t received them yet. You can check information about all channel releases for Office 365 client applications here.

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Note: if you don’t have the correct version of Excel 2016 then you won’t see the charts.

Types of Excel Map Charts

Excel map charts can display your data encoded by:

  1. Values with a 2-color scale:
  2. Office 365 map charts

  1. Values with a 3-colour diverging scale:

types of excel map charts

  1. By category:

excel map charts by category

Excel uses the Bing map engine to plot the data.

Inserting Excel Map Charts

First of all make sure your data includes some location information like country, state, region or county.

It’s important to make sure the locations aren’t ambiguous. For example, in Australia we have a state with the abbreviation WA, but there’s also state in the United States with the abbreviation WA, so we need to make it clear which WA we mean.

We do this by including columns in our map data for the country as well as the state like so:

columns in map data

Select the table of data > Insert tab > Maps > Filled Map Chart:

insert excel map chart

Tips for naming your columns, with thanks to fellow MVP, Ingeborg Hawighorst:

  • United States – Country and State – State can be abbreviated
  • Australia – Country and State – State can be abbreviated
  • UK – for Counties within the UK use Province
  • New Zealand – Regions should be written with an ‘s’ as opposed to Region singluar

With Category Map Charts each state/region/country can only have one category. In the example below you can see that each state is assigned to one type of store. Makes sense right?

category map chart

Tip: notice how the states are not disambiguated by a country column. I guess Bing is being clever and using the context of the other states to assume the WA state I’m referring to here is the one in Australia. However, if I had data for states or regions in other countries in this table as well then I’d definitely need to add a country column to my data.

Formatting Map Charts

With the map selected you can use the contextual Chart Tools - Design tab:

formatting map chart

And Format tab:

map charts format tab

Double clicking on the chart opens the format pane on the right-hand side:

map charts format pane

Tip: You can left-click on elements in the chart to select a different element to format.

Add labels (1 and 2); double click the label to open the format pane where you can choose from series name, category name and value (3):

map charts labels

Select the map itself to view the Format Data Series pane where you can choose the chart icon (2) to expose the Series options and Series colour settings:

format data series

Tip: the Map Area drop down list under Series Options allows you to choose whether you see the whole world, the region, only regions with data or multiple regions. By default it’s set to ‘Automatic’ which means it will adjust to suit the data.

Confidence Warning

If you see a yellow ‘i' icon in the top left of your map, like this:

map chart confidence warning

Clicking on it will reveal a message similar to this:

confidence warning message

You see this message when 10% of more of your data could not be plotted in the chart. Go back to your chart source data and reduce ambiguity by adding columns with more location information e.g. country, region, state, county etc.

Of course it may also be a result of spelling errors 😉

Map Chart Limitations

  • Map charts can only plot high-level geographic details in a one-dimensional display. Cities, latitude and longitudes, and street address mapping isn’t supported.  

    However, you can plot data at a postcode level, but this is generally ineffective because some postcodes have a small geographic area, for example cities, compared to areas outside of cities, and so it can be difficult to see the city data on the chart.

  • Map Charts are not available for PivotTables. Instead use this technique.
  • Map Charts do not allow you to insert a custom chart title by referencing a cell in the worksheet. A workaround to this is to use a shape/text box for your chart title instead. A tip from MrExcel: leave the chart title field under the text box so the map doesn’t take up the space where you want to display your text box. Enter a few spaces in the title box so you don’t see the default ‘Chart Title’ text.

Where are my Maps

If you have Excel 2016 on an Office 365 subscription and you can’t see the Map Chart icon then it’s likely you’re on the Deferred Channel, which hasn’t received them yet. You can check information about all channel releases for Office 365 client applications here.

If you have Excel 2016 but aren’t on an Office 365 subscription then you won’t have access to the new Map Charts.

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel Charts
Previous Post:Excel VBA String FunctionsExcel VBA String Functions
Next Post:Excel TEXTJOIN Function

Reader Interactions

Comments

  1. aaron

    March 3, 2022 at 3:08 pm

    Hi

    I am creating a map Example in excel now it is showing a map of Australia 🙂

    Issue One is Tasmania sales are not showing on the map ?
    Issue Two is New Zealand and Papua new Guinea do not show up on the map 🙁
    Issue three can you have a target and sales show up on the map or do you require two separate maps ?

    How do i get this to Work is it possible ?

    Country State Sales
    Australia ACT $100
    Australia NSW $200
    Australia NT $300
    Australia QLD $400
    Australia SA $500
    Australia TAS $600
    Australia VIC $700
    Australia WA $800
    New Zealand NZ $900
    papua new guinea PG $1,000

    Your assistance is greatly appreciated

    BR Aaron 🙂

    Reply
    • Mynda Treacy

      March 3, 2022 at 5:06 pm

      Hi Aaron,

      You have mixed states and countries in the state column. Excel doesn’t know what to do here because you’ve called it ‘State’. You need to have consistent data in each column, either all states, or all countries. In other words, you can’t break the Australian data down by state, but have the NZ and Papua New Guinea data at country level.

      Mynda

      Reply
  2. Rutayisire Reverien

    September 17, 2021 at 7:37 pm

    Hello.While inserting a map chart in excel, I got the following message:
    “Map charts work best with geographical data such as state/province and country/region in separate columns. Check your data and try again”.

    Please help me fix that error.
    Thanks a lot,
    Reverien

    Reply
    • Mynda Treacy

      September 18, 2021 at 8:58 am

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  3. Jodie

    December 3, 2020 at 9:28 pm

    Hi,
    For Australia, South Australia, can I drill down to Local Government Areas, also known as Councils, (which are the same as Counties in the USA)? I can drill down to postcode (zipcode) but cannot seem to work out how to take it back a step further to show Council areas/catchments.

    Reply
    • Jodie

      December 3, 2020 at 9:32 pm

      For example:
      Country State Council Number of consumers
      Australia SA City of Charles Sturt 4
      Australia SA City of Marion 2
      Australia SA City of Playford 10
      Australia SA City of Onkaparinga 6

      Reply
    • Mynda Treacy

      December 3, 2020 at 9:37 pm

      Hi Jodie,

      No, Excel map charts aren’t aware of our council areas, sorry.

      Mynda

      Reply
  4. M

    September 28, 2020 at 2:14 pm

    It’s completely useless. Only recognizes the first 12 items.

    Reply
    • Mynda Treacy

      September 28, 2020 at 3:00 pm

      No it doesn’t. Map charts will plot as many items as you give it. Sounds like the range you’re referencing is only 12 rows high and doesn’t include all of your data.

      Reply
  5. Erik Graham

    July 1, 2020 at 3:59 am

    First of all, let me just say your excel course is awesome. Anyway, I’ve got data at 3 levels of detail, county, state, and US total, all of which populate the sheet dynamically as the user selects from drop down menus. I have no problem map charting any of the data… BUT I can’t seem to figure out a good way to dynamically change the level of detail the map shows. I’m not talking about zoom level, I mean actually changing the underlying data that is being mapped from a county to a state (or national) level of detail. For example, if I’m viewing data at the county level, I have no problem switching counties, even switching to counties in a different state. But if I switch the level of detail to “State” I can’t get the map chart to adjust (reliably). I made some progress using dynamic names as the data source, but it often broke. I’ve resorted to stacking 3 identical charts, one for each level of detail. Then I use a macro to push the appropriate chart to the top of the stack, blocking the other two from view, when the user makes a selection. It’s not elegant but it works. Any better approaches?

    Reply
    • Mynda Treacy

      July 1, 2020 at 1:19 pm

      Hi Erik,

      Great to hear you’re enjoying the course. Can you please post your question in the forum and upload your Excel file (anonymise the data if required), that way I can see what you’re working with. If we can’t find a consistent solution then you could use this technique to select a pictured based on a cell value rather than the VBA.

      Mynda

      Reply
      • Erik Graham

        July 3, 2020 at 9:28 am

        Will do (but going on vacation for a week… so it will be awhile). Meantime, thanks.

        Reply
  6. Spencer

    May 19, 2020 at 5:58 pm

    Hi! For some reason, I can’t get Norway to appear. I’ve ensured proper spelling and even tried Norway’s two official names (Norge and Noreg). What am I missing? All the other countries in my dataset work fine but Norway, no.

    Reply
    • Mynda Treacy

      May 19, 2020 at 8:04 pm

      Hi Spencer, I’m not sure what’s wrong with the map chart. I’ve escalated this as a bug to Microsoft. I’ll let you know what they say. Mynda

      Reply
      • Spencer

        May 20, 2020 at 4:57 am

        Thanks Mynda! I’ll look forward to that. I really appreciate the help!

        Reply
        • Mynda Treacy

          May 20, 2020 at 9:40 am

          Hi Spencer, it’s a bug. Microsoft have logged it and will release a fix, but I don’t have any ETA for it, sorry.

          Reply
  7. Hanna

    April 29, 2020 at 2:44 am

    Hello! Firstly thanks for this tutorial; super helpful! I’m creating a map to show geographic coverage of my team state by state in the United States. Two of my team members cover FL, so I wanted to fill the FL data point with a lined pattern instead of a solid fill. When I go to format the data point, none of the patterns work but I can change colors, edit the shadowing, etc. Have you come across this issue before or do you have any ideas on how to fix? Thanks!

    Reply
    • Mynda Treacy

      April 29, 2020 at 10:18 am

      Hi Hanna,

      Map charts only allow for a gradient fill based on values. You can’t choose different fill for different states, which will explain why the patterns don’t get applied.

      Mynda

      Reply
  8. Bukenya Ivan

    March 16, 2020 at 6:26 pm

    Hi Mynda, my inquire is that a from Uganda, so, how can I Map my data putting into consideration that in Africa like my country, we don’t clear state points that can easily be identified by whether Bing or Google, and yet at times I would like to represent my data state (districts). Any advise.

    Thank you.

    Reply
    • Mynda Treacy

      March 16, 2020 at 7:14 pm

      Not easily. Please refer to this post for some ideas on building your own maps.

      Mynda

      Reply
  9. Sharon Burns

    February 4, 2020 at 12:11 am

    My mapping looks fine – I can get the ‘dots’ to populate but then they disappear when I click off the map to go to save, or print, or do something else. Tips?

    Reply
    • Mynda Treacy

      February 4, 2020 at 4:46 pm

      Sounds like a bug. The file might be corrupt or just the map chart. You could try recreating it, or recreating it in a new workbook.

      Mynda

      Reply
  10. Maurice Price

    November 8, 2019 at 11:08 pm

    Many thanks Mynda, Can’t tell you how much this is helping our project. We are aiming to provide services to difficult to define families. Essentially there are “indicators of need” but in table form this is a nightmare for decision makers. Being able to present this in map form has been like having people who are lost in a deep, dark cave and then turning the lights and signs on. thank you.

    Reply
    • Mynda Treacy

      November 9, 2019 at 8:43 am

      🙂 great to know you’ll be able to make use of the map charts, Maurice.

      Reply
  11. tarek

    October 13, 2019 at 8:47 pm

    is it possible to utalizr this feature with different countries around the world. let’s say Yemen for example. does Excel have a list of provinces of Yemen.

    Reply
    • Mynda Treacy

      October 14, 2019 at 7:56 am

      Hi Tarek,

      Excel map charts use Bing maps to plot the data. In theory it should be able to plot data for any country, but I have seen issues with countries outside of the U.S. e.g. New Zealand isn’t always plotted correctly. I can’t say for Yemen, but you can try.

      Mynda

      Reply
  12. Vivek Atmakuri

    September 18, 2019 at 6:18 pm

    So I created a map chart with three countries and 5 cities in each. Each city had a certain concentration of employees. When I click on the slicer for all Australian employees, the world map changes to Australia but does not give me a gradient mapping of Australia.

    I am trying to achieve this, Is it even possible?

    Reply
    • Mynda Treacy

      September 18, 2019 at 7:54 pm

      Hi Vivek,

      Map charts are not available for PivotTables, so I’m not sure how you’ve implemented it to respond to a Slicer, but you will have needed to reference a dynamic named range. Perhaps you can post your question and Excel file on our forum where we can help you further.

      Mynda

      Reply
  13. John Frederick Rosas

    August 28, 2019 at 8:33 pm

    Good day,

    May I ask for an assistance regarding classification of region/zip code/state? Neither of the said categories works with my country, Philippines. What should I use and where can I get a guidelines on which classification should I use on which country the next time should I use this map method.

    Thank you very much for your help.

    Reply
    • Mynda Treacy

      August 29, 2019 at 9:48 am

      Hi John,

      I’m not sure what data you’ve provided to the map, but try including the following columns country, region or state or county. You may need to play around with the last 3 to see which name Bing uses for the areas in your country.

      Mynda

      Reply
  14. Ngozi

    April 8, 2019 at 8:39 pm

    Dear Linda,

    thanks for your prompt response on unavailability of Fill Maps on my systems. i am currently an MS 365 user and still dont have it. what can i do to have access to it

    thank you
    ngozi

    Reply
    • Mynda Treacy

      April 8, 2019 at 10:39 pm

      That’s unusual because Office 365 automatically updates. You can try manually updating it by going to the File tab > Account and clicking on Office Updates.

      Reply
  15. Ngozi

    April 3, 2019 at 7:35 pm

    Dear Linda,

    I observed that you have 2 types of maps on Map Insert tab- Fill Map and 3D Map. I only have 3D map on Excel 2016. I find Fill maps easier since i was introduced to it on your page.
    How can install the Fill maps chart on my excel.

    Thank you

    Ngozi

    Reply
    • Mynda Treacy

      April 3, 2019 at 7:58 pm

      Hi Ngozi,

      Only Office 365 users have access to filled maps. It’ snot something you can install. You either have it or you don’t, sorry.

      Mynda

      Reply
  16. Edy Mochamad Suprapto

    February 7, 2019 at 8:13 pm

    hi there,
    im from indonesia, you mentioned above that postcode was ineffective. i use zip code for usa country and work well, but if i use post code for indonesia country not working..
    could you give me suggestion?
    thanks

    Reply
    • Mynda Treacy

      February 8, 2019 at 9:37 am

      Hi Edy,

      You could try changing the column header cell to ‘zip code’ instead of ‘post code’.

      Mynda

      Reply
  17. COLLEEN REIDY

    April 28, 2018 at 5:18 am

    THE LEGEND FOR MAP CHART WILL ONLY DISPLAY 20 CATEGORIES. THE MAP IMAGE DISPLAYS ALL 21 CATEGORIES. ANY IDEA HOW TO FIX THIS

    Reply
    • Mynda Treacy

      April 28, 2018 at 8:10 pm

      Hi Colleen,

      I’d say you have to make your chart bigger to allow enough room for all of the category legend items. Try different positions for the legend; top, bottom etc.

      Mynda

      Reply
      • Tony

        July 31, 2019 at 8:26 am

        Hi Mynda,

        I have the same problem. The chart would only display 20 categories no matter how big the chart is. Do you have any solution to this? Thank you!

        Reply
        • Mynda Treacy

          July 31, 2019 at 10:23 am

          Sounds like a limiation with the map chart. If you’re able to share a sample Excel file on our forum we can take a look and see if there is a workaround.

          Reply
  18. Jaime Roberts

    November 11, 2017 at 6:39 am

    Hello,
    If you own three businesses, and are trying to plot on a map across two states where the postal codes of each are and where they overlap, how do you go about this. When I highlighted all the data for one store, it only showed the data within one county. I then tried to Add Data Series, and that didn’t seem to work unless I am not doing it correctly or there is a better way to continue adding data to the same map. Can you please help? Thank you, Jaime

    Reply
    • Mynda Treacy

      November 11, 2017 at 2:07 pm

      hi Jaime,

      Map charts can only handle one set of data i.e. one series. If you want to segment that series by categories, as in the example no. 3 above, then you add a column to your source data that defines the category, but you can’t have multiple categories for the same state, region, post code etc. because Excel won’t know which one to show in the chart. In other words, overlapping can’t be displayed.

      Mynda

      Reply
  19. MAR

    July 13, 2017 at 2:42 am

    what other countries can you add states/provinces etc?

    Reply
    • Mynda Treacy

      July 13, 2017 at 8:47 am

      Hi Mar,

      As far as I know it works with all countries, but I’ve not tested it.

      Mynda

      Reply
  20. Duncan Williamson

    March 17, 2017 at 1:32 am

    Interesting Mynda but I want to have a moan!

    I moan to Microsoft but no one listens there so you are an Excel MVP and the nearest I have got to them!

    I have been an Office 365 Pro Plus subscriber for a long time now and that has got me Office 2016 or something really similar so I suppose I should be grateful for that. I have known for a long time that I am in the deferred channel and that explains why it took MS NINE MONTHS to deign to give me Office 2016.

    I do a lot of Excel training and many of my delegates are completely baffled by the market segmentation that sees many of them locked out of key features if they are on the wrong Excel path … and by the way, they had no idea, for example, that they had been locked out of, eg Power Pivot because of the Office 365 subscription they have.

    It come home to me today and yesterday how cheap we are made to feel when I downloaded a file from Chandoo and was told that his version of Excel is newer than mine. Today I wanted to work through this map tutorial but to no avail.

    Off my chest now and I am not blaming you or Chandoo in any way at all: however, you are both MVPs and possibly likely to have some sympathy!!

    Reply
    • Mynda Treacy

      March 17, 2017 at 9:39 am

      Hi Duncan,

      We MVP’s have been voicing this concern for quite some time. Recently they added Power Pivot back into the Excel Professional bundle, so that was a small win.

      As for the deferred channel, the good news is this is something you can choose and has nothing to do with how much you pay. By default Office 365 ProPlus is on the deferred channel, but you can fix that. Here are the instructions:

      https://support.microsoft.com/en-us/help/3185078/how-to-switch-from-deferred-channel-to-current-channel-for-the-office-365-suite

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.