Sometimes Excel 365’s Linked Data Types are great (Stock and Currencies) and other times they’ll drive you crazy or lead you to use wrong statistics. Here’s some problems we found trying out the, supposedly, simple City data type compared to the similar Geography and Location data types.
Just getting the right data can be a challenge with incomplete selections or worse when Excel just drops in the wrong data without asking.
Then there’s ‘fun’ with empty data (#FIELD errors) or statistics that aren’t comparable.
All this is just to show that when you get past Microsoft’s hype about Excel’s Linked Data Types, you can take the numbers given at face value.
Find the right location
We started with a list of major world cities which you’d think would be a ‘doddle’ for the City data type … think again.
Excel 365’s City data type seems to have an American fixation. For both ‘London’ and ‘Paris’ the Data Selector appears to can choose which one you want from the many locations with the same name. But NEITHER selector includes the famous cities of that name. Only US cities and towns.
This seems to be a Bing problem that we’ve noticed before, Why people prefer Google over Microsoft’s Bing
At least for ‘Sydney’ there’s the choice between our boss’s home city and the lovely Canadian town.
Beware automatic selections
Be careful when Excel adds a data type automatically without a selector. ‘Munich’ seemed to be added without a problem until we looked at the area and population. Munich is 3.7 square kilometres with only 195 people? … I don’t think so.
Excel has dropped in Munich, North Dakota, USA … not the major German city. There should be a data selector to choose but Excel seems to have added the town because that’s the only name match in the USA.
Which data type to choose?
Excel 365 now has three different geography related data types. Geography has been there since the beginning and now there’s also City and Location. The difference between them isn’t clear at all. We tried the same famous cities with each of the three data types.
Geography data type
Geography is at least complete with no #FIELD errors. The top selections for each city were the most likely / well known choice. As we’ve noted before, the Area values aren’t comparable. New York City’s 1,214 km2 seems to be the combined land and water area. London’s 1,572 km2 is just one of the possible answers which range between 2.9 km2 and 8,382 km2 depending on your definition of ‘London’. The Paris area is preposterous but, like London, there are several boundaries that can define the city (between 105 km2 and 18.940 km2)
City data type
City data type, we’ve already mentioned its North American fixation.
We’re wily enough not to ask why it can’t show an area for three world famous cities … that way madness lies.
Notice that the population numbers are different from those using Geography data type, some are minor, probably scraped from separate locations. The Sydney population difference (1.3 million) seems to be comparing statistics from very different years or areas considered part of ‘Sydney’.
Location data type
Location seems to be an enlarged set of the same information used for City. That’s why both data types have the same population numbers but nothing for Area.
At least Location understands the nature of a whole planet by showing data selectors for London, Paris and Munich with the major cities as first choices.
Don’t trust Excel’s Linked Data Types, always verify
More linked data types coming to Excel 365
Excel 365 now has Nested Linked Data Types
Custom Data Types are great in Excel 365
Fix Excel when Stock and Geography data types go missing
Stock and Geography Data Types coming to Office for Mac
Stock data type search tips in Excel
