New in Excel 2016 (for Office 365 subscription users only*) are 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
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:
- Values with a 2-color scale:
- Values with a 3-colour diverging scale:
- 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:
Select the table of data > Insert tab > Maps > Filled 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 Countries 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?
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:
And Format tab:
Double clicking on the chart opens the format pane on the right-hand side:
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):
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:
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.
If you see a yellow ‘i' icon in the top left of your map, like this:
Clicking on it will reveal a message similar to this:
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.
If you liked this please click the buttons below to share.