Microsoft on Thursday leaned more heavily into its support for “data types” within Excel, going beyond the staid concept of numbers and formulas to embrace a huge variety of elements that could reside within a single cell.
Microsoft calls data types the “third dimension” of an otherwise two-dimensional Excel cell, and it’s easy to see why. In 2019, Microsoft introduced the first two data types: “stocks” and “geography.” If you input a stock symbol into a cell, and then identify it as a stock symbol using the Data > Data Types > Stocks identifier, the term “MSFT” opens the door to numerous fields of live data which can be set as separate cells, columns or more. You can always get the latest data by tapping the “Refresh data” button within Excel.
Microsoft has also partnered with Wolfram Alpha to take hundreds more data types and bring them into Excel, everything from exercise to food, chemistry, space, universities and their tuition and more. What’s more, Microsoft is now allowing users to define their own data types, and bring them into the related Power BI service as their own “customer” data type, defined however they wish. If you enter data into an Excel spreadsheet, you’ll be able to create your own custom data types and query them using Power Query. Microsoft has a couple of videos that help provide more context on what this means in real life; here's its flashy promotional video on data types as well as a second, more specific video providing a brief overview on using "customer" or your own personal data.)
Data types sounds like a feature that would be relegated to an accounting firm, but it’s a foundational technology that can really be applied to a number of different applications, including this DIY diet analysis:
For now, the Wolfram data types are relegated to the Office Insider channel, and most aren’t quite live yet. You’ll also need a Microsoft 365 subscription, as well as the appropriate Power BI subscription, to take advantage of the Power BI features. But Brian Jones, the head of product for Excel, also wrote in a blog post that the company plans to debut a new set of “smart templates” that will enable people to get started quickly with data types, and “do things like track their nutritional information, research potential colleges, monitor stocks, learn chemistry, and much more.” Unfortunately Microsoft didn’t give a timetable for this.
Microsoft has allowed Excel users to pull data from live sources for years, though the new data types allow for constant updating. The Collections feature within Microsoft Edge sort of flips this on its head, allowing you to “collect” several webpages and then track their attributes inside an automatically generated spreadsheet. All of this points to a future in which data is tracked, measured, and reassessed in real time.
Updated at 9:34 AM with more detail, including links to videos.