How To Use Excel Data Types and When You Might Not Want To

Microsoft recently introduced a feature called “Data Types” into Excel 365. Unlike the regular Excel data types like Dates, Numbers, and Text, this feature relies on the internet to bring external data points into your Excel sheets. They call them linked data types. The implementation is a lot easier to use than creating a query or using VBA to bring data to Excel, but it’s pretty limited in its current form and probably wouldn’t be very valuable to most users. Right now, it’s limited to Stock and Geography data.

Converting a set of stock tickers to the Stocks data type.

Once they are converted to the proper data types, info about them can be retrieved using the cell reference and a period. To get the AAPL price in the above screenshot, you would use =A2.Price

Stock card

Once converted to a linked data type, cells are prefixed with a small icon indicating their type, which reveals a card containing information and a graphic for the cell. This is nice for interactivity, but ultimately not something most users would need to use frequently on their own sheets.

Where It Falls Short

Don’t get me wrong, I think that these new linked data types are another great step forward for Excel with regards to the ability to make use of external data in the program. However, there’s so much missing from this feature right now that limits the usefulness. Firstly, it’s limited to the two data types, but I expect this will change soon. Having stocks be one of the first types makes perfect sense in order to help Excel compete with Google Sheets’ Google Finance connectivity and appeal to the analysts that rely on the program. What is more concerning for me is the inability to provide parameters to narrow down your query. I can get the price for a stock at the time of refresh, but what if I want the price for the last Monday? Suddenly, linked data types are useless because you can’t specify what day you want the price for.

What To Do In the Mean Time

If Microsoft believes in this feature and continues moving forward with it, I have no doubts it can serve as the primary means of bringing internet data into Excel in the future. In the mean time though, if you need a bit more flexibility, check out my Get & Transform Query tutorial.