In an blog post published in 2019 (“Excel Map Charts with UK Data Sets“) I identified some limitations and errors in the way that Excel’s ‘Filled Map’ chart option dealt with UK locations and areas. I’ve now updated the spreadsheet of area names that need to be used for this to work.
If all you want is the spreadsheet, here it is! But you might want to read the following too…
The main problem with the original was that the areas used were based on the ‘historic counties’ of the UK, rather than current administrative areas. This meant that if you were, for example, using data from the Office for National Statistics, you would find mismatches between the data set and the areas ‘plottable’ in the map chart. In addition, some areas just wouldn’t plot, or plotted to entirely different areas!
The ‘Filled Map’ chart has now been improved and now reflects the current administrative areas of the UK – ‘historic counties’ have been replaced by the system of ‘two-tier’, ‘unitary’, and ‘metropolitan’ authorities … kind of …
It’s an improvement, but there are still some exasperating issues.
First the good news:
Maps of Wales and Scotland are Fixed!
As I described in my 2019 post, some areas just didn’t plot, no matter what versions name and punctuation you tried. Woe betide you if you had data to plot from West Dumbartonshire or Monmouthshire. It was as though they didn’t exist. But I can now report that mapping the current administrative divisions of Scotland and Wales seems to work fine.
Mapping Northern Ireland: A New Look Entirely
In 2019, the only divisions that could be plotted on maps of Northern Ireland were the historic counties of Antrim, Armagh,(London)Derry, Down, Fermanagh, and Tyrone. These have been replaced by the current administrative areas:
England: Better, but some “features”
So, now the filled map chart will accept the names, and allow plotting of data from:
- Two-Tier County Councils
- Unitary Authorities
- Metropolitan Counties and Districts
This does mean that if you are working with ‘county’ level data you’ll need to take account of the fact that as far as Excel is concerned, counties do not include the unitary authorities that fall within their borders. So here’s historic ‘Essex’, with its two unitary authorities which are now separate entities, Thurrock and Southend-on-Sea shown in dark blue.
If you plot “Essex”, you get the light blue area with unitary bites taken out of it.
But here’s the list of more significant issues. I should make it clear here that in the absence of documentation, these are based on trial and error, hunches, and sometimes rather abstruse knowledge of the political history of the UK.
East Riding of Yorkshire and Kingston-upon-Hull
This is probably the most serious issue with the plotting. The East Riding of Yorkshire is plotted to the area of Kingston-upon-Hull – which is actually a different authority, which doesn’t plot at all. The actual area of the East Riding does not plot.
Out of interest, if you select just the ‘East Riding’ line in the spreadsheet and ask for a filled map chart, you get a nice outline of … you guessed it … Kingston-upon-Hull.
The root of the problem may be in the areas of Goole and Snaith which used to be in West Yorkshire (they are south of the Humber) but are now in the East Riding. Perhaps this has thrown the whole section of the map out.
Edit: You can, if you delete the chart, delete the ‘Kingston-upon-Hull’ line in the spreadsheet, retype it and then make a new chart, sometimes get Kingston-upon-Hull labelled correctly. But this is hardly confidence-inspiring or reliable, and it doesn’t fix the ‘East Riding’ problem.
St. Helens – Metropolitan District
Neither “St Helens” nor “St. Helens” plot. Or “sthelens” or anything else I could think of. If you can make it work, let me know.
Bournemouth, Christchurch and Poole
The Unitary Authority of Bournemouth, Christchurch and Poole (BCP) does not plot. Instead, Bournemouth and Poole are plotted separately and Christchurch is shown as part of Dorset.
This may reflect the fact that when BCP was formed, there were objections from Christchurch Borough Council. which wanted to remain in Dorset, and pursued this through the courts (and lost). The Excel map does not reflect the actual administrative divisions as of 2021.
While other Metropolitan Counties are divided into their Metropolitan Districts, Greater Manchester is not so divided.
Other Metropolitan Counties
Some of these plot something, but not the whole county – usually just one part. ‘Merseyside’ plots just the Wirral for example.
The London Boroughs still aren’t plotted separately, despite the value this would bring. Luckily, there are other tools for this such as this one from the London Datastore.
So, in summary …
Some of the issues with the original filled map chart have been resolved, but there’s still work to do on this. The ‘East Riding’ issue means that for the straightforward task of ‘take data from every authority in England/the UK and put it on a map’, this tool isn’t adequate.
It would also be very useful to see data from regions (South-east/East/Midlands etc) and major cities properly handled as in many cases users will want to present data in ways other than by county council or unitary authorities. There are tools which will allow this – such as various paid Excel add-ons, or PowerBI – but for the ‘casual data scientist’ it would be great to have this ready to go and working consistently in Excel itself.
Here’s the updated Excel spreadsheet with the area names for England, Scotland, Wales and Northern Ireland – please download and use – and get in touch if you find any issues, need help, or find solutions to the problems listed above.
Spreadsheet Link: Excel Map Charts for the UK 2021