For most of its existence, Microsoft Excel has supported two main types of data: text and numbers. But Microsoft has been working for years to “up-level” the types of data supported in Excel and now is enabling customers to work with their own data as a data type.
Microsoft already had taken a couple of steps toward expanding the types of data types supported in Excel. It introduced both dynamic arrays and array formulas and support for Stocks and Geography data types via Bing.
TechRepublic: 60 Excel tips every user should master
On October 29, officials announced “the next step in that journey,” meaning the ability for customers to use their own data as a data type in Excel. Instead of having to force data in a two-dimensional grid, Microsoft is enabling any cell to now contain a rich set of structured data, giving it a third dimension officials said.
Here’s Microsoft’s explanation as to how this will work:
“If, for example, you have a system for tracking your customers, and you want to bring that customer data into Excel for analysis, now you can import as a richly structured ‘customer’ data type structured in the way you or your company have defined ‘customer.’ If you are a Power BI customer, Excel will automatically connect to Power BI, and any data you’ve published will be easily discoverable and flow into Excel as a structured data type. If you have data you’d like to work with as a data type in Excel, just publish it into Power BI, and the rest is taken care of for you and anyone else with access. If you don’t have Power BI, you can still leverage the Power Query technology in Excel to manually connect to dozens of different types of data sources, and specify that you want the data to be structured as a data type.”
Microsoft also announced today that it is expanding the number of public data types available to customers. It has formed a partnership with Wolfram Alpha which will allow “hundreds” of new data types to be available to Excel. Among the new additions: Chemistry, nutrition, zip codes, historical events and genetics. Those interested can start test driving these via the Office Insider program.
Power BI data types are available in Excel for Windows (in the Current Channel) for all Microsoft 365/Office 365 subscribers who have a Power BI Pro plan. Power Query data types are rolling out and will be available in the Current Channel soon for all Microsoft 365/Office 365 subscribers for use with data sources supported by their particular SKUs. And the Wolfram Alpha data types are available in preview for Office Insiders.