Excel 365 for Windows is getting an expanded Linked Data Types feature with more categories of data and extra features. Here’s what’s possible and the traps that Microsoft doesn’t mention.
Stock, Currency and Geography data types have been in Excel 365 (Windows and Mac) for some time. Now it’s expanding into other areas via Wolfram data services.
At the moment, the expanded data types are available in the Current Channel (Preview) (aka Insiders Slow) release of Excel 365 for Windows. That means the feature is near public release. Office for Mac insiders can also try out the new Linked Data Types.
We’ll try Movies (called Films in Excel for Mac). Select the cells then choose Data | Data Types | Movies.
Excel will contact Microsoft’s servers and grab all the information about those movies. The cell will change to show a little icon to indicate its now ‘home’ to linked data.
Data Type Card
Select a linked data cell, right-click and choose Show Data Type Card. This shows all the information (fields) available for that item.
Field List
Click the little icon at top-left of a linked data cell to see what additional fields are available.
We’ll choose Director, it’s added to the next column.
See that the Director cell also has a data type icon? That’s because of a nice extra Excel 365 now has Nested Linked Data Types
Use a Table
This step is optional but very helpful. Make the list into a table (Home | Styles | Format as Table).
As a table, the columns are automatically filled when you select another field and the field name is added to the table heading. And there’s all the usual Excel Table goodies such as automatic formatting, sorting and filtering.
Fixing Linked Data
The linked data needs some fixes before you use them fully. There are a few glitches in the way data is presented, Microsoft might fix some of these bugs, but they’ve shown no interest so far.
Dates arrive in General format and have to be converted to Excel Data format.
Currently the field names are lower-case and probably need capitalizing in the table headings.
Image fields can’t be centered in the cell.
We’ll talk more about data issues on other articles:
Be careful with Excel 365’s Linked Data Types
Don’t trust Excel’s Linked Data Types, always verify
For the moment, notice some anomalies in the table above. ‘Worldwide box office total receipts’ is missing for two films entirely (#FIELD means there’s no data for that field). The value for Citizen Kane is presumably the initial takings in the first year or first release only because there’s no way that such a famous film has only earned a million dollars over 80 years of repeated screenings.
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
