Microsoft is giving its popular spreadsheet application Excel a much-needed update with support for custom live data types.
Over the last few years, Microsoft began adding different data types to Excel, allowing users to add data like geography and real-time stock data from the cloud. Due to a partnership with Wolfram, a web and cloud software company, Microsoft has built up a bank of over 100 pre-set-up data types.
With the new update, Excel users will no longer be limited to pre-built data types. This means users will be able to manipulate data in Excel in new ways and refresh the data in the cell to keep it up to date.
Users will be able to convert cells into data types. Once the cells have been converted into linked data types, users can extract additional information to work within the spreadsheet. This means if a company enters a product code or name, they will be able to see additional information like a description, price, and available inventory all within the card view.
The Verge gave an example of users being able to import the data type for Seattle and then create a specific formula that references that single cell to pull out information on Seattle’s population.
“Today, we are taking the next step in that journey, and allowing customers to work with their own data as a data type. 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,” announced Microsoft on its website.
The conduits for the new feature will be Power BI and Power Query. The Power BI service will act as the connector to bring data sources into Excel data types on the commercial side, allowing businesses to connect a variety of data. For Power BI customers, any data they publish will be easily discoverable and flow into Excel as a structured data type.
The Power Query allows users to transform uploaded data into a data type. This can include information from files, databases, websites, and more. The data can then be cleaned up and converted into a data type to be used in spreadsheets.
“Up to this point, the Excel grid has been flat: it’s two dimensional. You can lay out numbers, text, and formulas across the flexible grid, and people have built amazing things with those capabilities. Not all data is flat though, and forcing data into that 2D structure has its limits. With Data Types, we’ve added a third dimension to what you can build with Excel. Any cell can now contain a rich set of structured data in just a single cell,” said Microsoft in a statement.
Power BI data types are currently available in Excel for all Microsoft 365 and Office 365 subscribers that have the Power BI Pro service plan. The Power Query data types are now rolling out and will be available to all Microsoft 365 and Office 365 subscribers in the coming weeks.