Excel’s new ‘Data Types’ Feature is the Apps Biggest Update in Years

If there is one app in the Office 365 suite that keeps people coming back to the productivity tools it is not Teams, but Excel. The spreadsheet application is easy to use, highly flexible, and in many companies around the world, worksheets are a structural part of the operational workflow.

Last year, when XLOOKUP was introduced, this was one of the first significant updates in several years that would have a direct impact on how users are interacting with data but what the company is announcing today, is even more significant.

Today, Microsoft is announcing that they are allowing customers to create their own data types and augment that data inside of a cell.

Let me explain. Data today is “2D” meaning, what you see is what you get but coming soon, data can become “3D” and this is a significant update to Excel and is one of the biggest enhancements in recent history.

Information in a cell in Excel, for the most part, is exactly that, a single data point represented at a period in time. Being introduced today is the ability to have data in a cell be a rich structured data set inside of a single cell. But more than just being a structured data set, you can extract and manipulate that rich data in other cells.

Image #1 Expand

This may be a bit confusing but here is an example. Imagine for a minute you have a population data type in a single cell, such as C17, by calling C17.population, you can extract the value of that population from a single cell and data set.

Sure, this sounds simple but when you have hundreds of cities and need to analyze populations and other metrics from that data set, this can save you hours of work. Previously, the easiest way to do this would be to have multiple sheets inside your Excel document that have the static data that is then linked with the necessary syntax to perform this analysis or computation.

A more recent example of this new feature in action is that Microsoft recently introduced Stocks and Geography data types and soon, you will be able to define your own data types from your own data sets.

The easiest way to do this is will be with PowerBI where any data published there will flow into Excel as a data type. For example, if you have customer data in PowerBI with revenue attributes, in Excel you could type Customer.Revenue and have that information ingested into your excel models for manipulation or report generation.

If you do not currently use Power BI, you can still leverage the Power Query technology in Excel to manually connect to data sources and specify that you want the data to be structured as a data type in your document. While not as easy as using Power BI, this is a viable option too.

This is a big step for Excel and will make it easier to utilize your data in a tool that many people inside your company are using every day. Granted, this is absolutely a power user feature but helping your users become advanced users of a tool that is accessed every day is typically much easier than trying to teach them a new tool (Power BI,).

As for availability, Microsoft says that Power Query data types are available starting today for Office Insiders. The feature will be coming to users in the Current Channel in the next few weeks, in the Monthly Enterprise Channel in the next couple of months.