Excel gets its biggest update in years with support for custom data types

Excel gains another dimension of power with custom data types.

What you need to know

• Microsoft announced that Excel will soon support custom live data types.
• The functionality allows you to import your own data as custom data types.
• These new data types allow you to place structured data within a single cell.

Microsoft announced a major update to Excel that brings the option to create your own data types. These data types can include live information that can greatly expand the functionality and relevance of data within spreadsheets. Excel can also work with Power BI and richly structured data within spreadsheets.

Microsoft shares context about data types in a new post. Previously, data was "two dimensional," meaning that people could place numbers, text, and formulas within a grid. These types of data can create complex spreadsheets, but they do have limits. Now, Microsoft has added a third dimension to Excel, allowing any cell to include a rich set of structured data. In other words, single cells can now include structured data that can be referenced by other parts of the spreadsheet.

In this context, "rich" means that a single cell can have references to other data types, images, intelligent actions, and a collection of hierarchical data. Microsoft explains the value of this new functionality:

Now a cell can contain a vibrant data type; one cell, a world of possibilities. Data types allow you to bring the data you care about into the grid, in its most natural form and unleash the power of Excel to gain critical insights. With data types, a single cell value can have a live connected set of information you can quickly pull into your decision-making process, without needing to constantly go back to the original source to find more information. Leverage the card view to see more information on that data type, and navigate through to related information.

Microsoft shares an example of a spreadsheet that relies on population data for the city of Seattle. You could import a data type for the population of Seattle and then have formulas that utilize that information.

Excel can also work with business data powered by Power BI. You can connect Power BI data in Excel as data types.

People can also have data within Excel transformed into a data type with Power Query. This feature allows you to add information from websites, databases, and files.

Microsoft also announced a partnership with Wolfram, which allows hundreds of new data types to be used within Excel, though this is in preview at the moment.

Power BI data types are available in Excel for Windows for Microsoft 365/Office 365 subscribers that have a Power BI Pro service plan. Power Query data types will roll out over the coming weeks for Microsoft 365/Office 365 subscribers. The Wolfram Alpha data type functionality is available in preview for Office Insiders.