Excel Map charts
Excel's, "Filled Map", chart allows you to colour in a map based on values from your worksheet. In the following example the Australian map has been coloured in various shades of green, depending upon the volume of sales in 2020.
This is a simple two-colour shading scheme whereby the highest value is a deep shade of green and the lowest value a light shade of green. But in a three-coloured scheme you can also identify a third colour the middle, where a different colour is defined. In the following example countries with a zero net migration are white, with shades of net immigration in green and net emigration in red.
Filled maps do not have to be shades of colour based on numeric value, but can be colour coded based upon a text value - such as this below map showing the results of the 2020 US presidential election.
Â
Creating a Filled Map chart
Creating a Map chart is the same as creating any type of chart. You just select a cell in the table that contains the data and then click the Filled Maps option in the Insert menu on the ribbon. There are lots of settings that govern the appearance of Filled Map charts, and if you're familiar with other types of chart then you'll probably have no problem finding them and setting them to your liking. But hereunder I will explain some of those settings specific to filled maps.
Â
The Format Pane
Formatting settings are editable in the formatting pane, which can be shown in one of two ways:
Select an element in the map and either use the keyboard shortcut [Ctrl] + [1]; or
Right-click and choose the format option at the bottom of the context sensitive menu.
Note. It's not always easy to select or right-click the correct element in a chart, and so excel provides a Chart Options drop-down to let you switch to one of the other elements you may have intended.
Â
Format Data Series
Map projection allows you to choose a method of distortion to use when presenting the curved surface of a globe onto a flat page. Distortions are more significant when the map covers a large area, and for small areas there's little distortion and most projections look alike.
Map area allows you to choose the breadth of the geography to be shown.
Map labels allows you to choose whether or not to label the geographical areas on the map.
The 'Series Colour' options allow you to choose the two or three colours to use when shading the map, and the values that those colours represent.
Â
Format Legend Entry
When filling a chart with colours based on text (such as the Biden/Trump chart) it is not a question of assigning a colour scheme to an entire data series, but to individual legend entries.
Excel has it's own default sequence of colours it will assign, but if the colours are important, such as blue for democrats and red for republicans, then you can go through them individually,
By selecting one (and only one) of the legend entries and then showing the formatting pane, you will be able to see the Format Legend Entry pane, that allows you to set the colour to associate with that one value.
Â
Accuracy of place names
Excel will take a reasonable guess when something less than the proper name is entered, but it won't always get it right and when confidence is less than 100%, will show something similar to this message at the top of the chart. But It's not always easy to work out which names are in doubt, what the proper names should be entered. The problems that can be caused by misspellings, typos, informal names or even old place names, can be significant.
At the end of 2020 the 'Geography' data type was rolled out to Excel 365 subscribers. Its primary purpose was to provide easy access to data such as populations, time-zones and capital cities; but it also made it much easier to find the correct spelling and disambiguate place names. In the above examples you will have noticed the flag icons in the cells where place names have been entered, and these indicate that the cells have been converted to the 'Geography' data type. For more details, see the related articles section below this article.
Â
Pivot charts?
You cannot create a Filled Map chart from a Pivot table, and if you try Excel will report an error message, as shown. This is highly convenient as it will very often be the case that the values you want to plot will be summary totals from another table.
Excel suggest you copy the data from the pivot table into another part of the workbook, and to create a filled map chart from the copied date. This obviously does work, but is dreadfully inconvenient as not only will you need to cut and copy the data, but you may also need to refresh the pivot table first.
So if on a regular basis you are going to want to view your summarised data in map form, then you should probably build your own 'mock' pivot tables, and because it only has two columns - one for the labels and one for the values - then that's probably not too difficult.
You may also benefit from building in some sort of check to ensure that all of the labels in the raw data, are listed in your mock pivot table, such a cell containing something similar to the following formula:
=if(COUNTA(UNIQUE(tblRaw[State]))=rows(tblMock[State]), "OK", "ERROR")In fact, you may find it useful to use the UNIQUE function by itself to list all of the unique statesto the side of your mock pivot table.
Â