Microsoft is overhauling Excel with the ability to support custom live data types. Excel users have been using Microsoft’s spreadsheet tool for decades to import, organize, and analyze data, but the basic data types have always been limited to text and numbers. While Microsoft has added dynamic arrays and some custom stocks and geography data types previously, the company is now updating Excel to let people import their own data as a custom data type.
This means you’ll be able to manipulate data in Excel in new ways and hopefully without much of the hassle that exists today. You could import the data type for Seattle, for example, and then create a formula that references that single cell to pull out information on the population of Seattle.
These data types work by cramming a set of structured data into a single cell in Excel that can then be referenced by the rest of the spreadsheet. Data can also be refreshed to keep it up to date. If you’re a student who is researching the periodic table, for example, you could create a cell for each element and easily pull out individual data from there.
Microsoft is bringing more than 100 new data types into Excel for Microsoft 365 Personal or Family subscribers. Excel users will be able to track stocks, pull in nutritional information for dieting plans, and much more, thanks to data from Wolfram Alpha’s service. This is currently available for Office beta testers in the Insiders program.
Where these custom data types will be most powerful is obviously for businesses that rely on Excel daily. Microsoft is leveraging its Power BI service to act as the connector to bring sources of data into Excel data types on the commercial side, allowing businesses to connect up a variety of data. This could be hierarchical data or even references to other data types and images. Businesses will even be able to convert existing cells into linked data types, making data analysis a lot easier.
Power BI won’t be the only way for this feature to work, though. When you import data into Excel, you can now transform it into a data type with Power Query. That could include information from files, databases, websites, and more. The data that’s imported can be cleaned up and then converted into a data type to be used in spreadsheets. If you’ve pulled in data using Power Query, it’s easy to refresh the data from its original source.
The key to how this will all work in practice is just how well Power BI and Excel can handle the original data being fed into it by businesses. Excel and Power BI will need to competently detect patterns in data, apply its logic to structure that data, and ultimately transform it into something usable inside a spreadsheet. While Excel users will be able to clean up the data that’s being processed, that’s often easier said than done.
These new Power BI data types will be available in Excel for Windows for all Microsoft 365 / Office 365 subscribers that also have a Power BI Pro service plan. Power Query data types are also rolling out to subscribers. On the consumer side, Wolfram Alpha data types are currently available in preview for Office insiders and should be available to all Microsoft 365 subscribers soon.