Skip to content

Don’t trust Excel’s Linked Data Types, always verify

Technically, Excel 365’s Linked Data Types are clever.  The problem is the reliability of the data.  We found data issues without even trying to find problems.

In general, the data sources are ‘scraped’ from sites like Wikipedia which explains some, but not all, of the omissions and oddities. 

Stock and Currency data types are, thankfully, from reliable sources usually the global stock exchanges themselves. 

Our first test of newer Linked Data Types revealed an omission right away.  The Millau Viaduct isn’t as well-known as it deserves but it’s country and city aren’t a secret (the city is in the name!).  It’s strange that a location and local map images can appear but not the names.

Linked Data Types don’t convert date fields into Excel dates to make sorting and filtering possible.  To do that select the data column and choose a date format you prefer (as we have in the Opening Date column).

#FIELD error means no data found

Here’s another, the Director of the Paddington movies doesn’t have a picture nor place of birth listed on Wikipedia, so Excel shows #FIELD errors.

A pity that Microsoft could not come up with a better error code for missing data than #FIELD which looks like some technical error.  #N/A is already used but how about #EMPTY or #UNKNOWN, something that’s clearly understood by us mere humans.

Choose the right one

Make sure that Excel chooses the right item for that Linked Data Type.  Here’s an example of how it should work with the two famous men (at least in Britain) called ‘David Mitchell’.

If unsure, Excel should display a Data Selector pane to help you choose the current one.

However sometimes Excel will insert the wrong linked data without checking.  This is especially a problem with Stocks and Currencies where there are many similar codes that are easily mixed up.

Always check Linked Data

Linked Data Types are great, but they are far from perfect.  Microsoft’s zeal to sell the feature ignores the traps and limitations.

We’ve talked before about the problems with the Geography data type showing incomplete or non-comparable statistics. 

It’s what happens when you rely on ‘web scraping’ from public web sites without proper curation or checking.

Trust a bit … Verify a lot

‘Trust but Verify’ is an old Russian proverb that very much applies to Excel 365’s Linked Data Types but we’d focus more on the ‘Verify’ than the ‘Trust’.

That especially when comparing values, such as the Area or Population of a city or country. The numbers given by Excel 365 might not be accurate.

If you need more convincing see Be careful with Excel 365’s Linked Data Types

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

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.