Connect to your own data with more new data types in Excel

The evolution of Excel

Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base types of data you can work with: text and numbers. We’ve been on a multi-year journey to up-level the types of data you can work within Excel, and you may have seen two examples of this already. The first was the introduction of dynamic arrays and array formulas, where a single formula could return an array of values. The second came with the introduction of the Stocks and Geography “data types,” powered by Bing. These connected data types meant for the first time, a single cell in the grid could contain a live, connected, and rich set of information about objects like stocks, currencies, cities, and countries. That single cell would be intelligent in how it worked with features like formulas, filters, and charts.

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.” 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.

We also continue to expand the number of public data types for you to work with expanding on the Stocks and Geography data types. We formed a partnership with Wolfram to allow hundreds of new data types to flow into Excel. Complement your analysis with connected data types like chemistry, nutrition, zip codes, historical events, even genetics. Join the Insider’s Beta program to get a sneak peek of these capabilities and give us your feedback.

The evolution of data types

Before we get into more details on the new types of data you can connect to, I want to provide a bit more context on why the data types platform is so important and how it will improve the way you work with Excel going forward. 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.

Data types are rich, going well beyond numbers and strings—just one cell value can contain a deep collection of hierarchical data, references to other data types, images, and intelligent actions. They are also connected and can point to live services providing data that can be refreshed at any time. This means data types offer you two key advantages over the old way of working with data, flexibility, and confidence.

The advantage Excel has over any other data/analytical tool is its flexibility. People are constantly evolving their spreadsheets to map the changing needs of their business processes. Until now, to get a value in a cell you either wrote a formula or typed the value directly into a cell. One cell, one value. If you wanted another value, you repeated the process; filling up the grid with a mix of disconnected data and helper cells. With data types, that all changes. 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. That additional information can be used in the formulas you write, charts you create, it can even be used to change how you sort and filter. This will allow you to build more vibrant spreadsheets, faster, with robust attributed data, and have less chance of error.

In addition to the flexibility, data types also give you more confidence in the data you are viewing. Avoid the risks of copy and paste errors. Import the data type for Seattle, and you can now use a formula to reference the population =A1.population. If the data type updates, the results of the formula will update too. And rather than just seeing the static number 729,601, you also can see the formula and you now know exactly where that number came from.

Data discovery and trust with Power BI

Since introducing Stocks and Geography last year, we heard it clearly that you want the ability to work with your own business data as data types. We worked closely with the Power BI team to provide rich, connected Power BI data in Excel as data types. Power BI is the ideal source, with its best-in-class service that allows for shared, controlled, and secure access to data from all kinds of corporate sources. Data Types, connected to Power BI, give you that flexibility and confidence when you work with your business data in Excel.

Just like Stocks and Geography, you’ll now be able to discover Power BI tables available to you through the data types gallery. You can then convert your cells into data types. Once cells have been converted into linked data types, you can then extract additional information to work within your spreadsheets. For example, if you enter a product code or name, you’ll see additional information like a description, price, and available inventory all within the card view.

Use those additional properties to do deeper analysis. Build invoices in Excel with connected and trusted data coming directly from Power BI.

Additionally, because data types remain connected to the underlying source in Power BI, you are able to pull data from Power BI without needing to flatten it or lose all the information about the source of truth for that data. Power BI provides some of the most advanced governance controls over datasets, including over 140+ data sources on-premises and in the cloud, permissions-based access, row-level security, full application life cycle management, the ability for any user to apply Microsoft Information Protection labels, and for IT to certify. So, you can trust you are getting the right data every time.

And of course, since Power BI data types are connected to live data sources, you can update your workbook by simply clicking Refresh Data.

Create data types from your own data

While publishing your data into Power BI gives you and your team an easy way to discover and import data types into Excel, you can also create your own data types connecting directly to almost any data source you want. Excel has always supported a large set of data sources for direct import into the spreadsheet, and thanks to Power Query, you can also build powerful queries and transforms to get the exact shape of data you’re looking for.

Now, when you import data into your spreadsheet, you’ll have the option to transform that data into a data type. In Power Query, you can create your very own custom data types using any source of organization data available within the Get Data menu—files, databases, online services, other sources—the list is long. From here, you can pull in, clean up and curate, and transform that data into custom data types according to your needs and preferences. These data types live within the workbook you’re building, allowing you to simplify large volumes of data and make it easier for others in the organization to consume. But the additional data is connected and available whenever it’s needed.

Power Query data types provide the ultimate flexibility in building out custom solutions in Excel. And, you guessed it, Power Query data types will automatically update from the connected data sources with a simple click of Refresh Data.

Wolfram Alpha data types—hundreds of additional data types coming to Excel

If you’re a Microsoft 365 Personal or Family subscriber in the Office Insiders program, you may have noticed that the Stocks and Geography data types have a few new neighbors in the ribbon. We are currently previewing the first several of over 100 new data types that will pull rich information from Wolfram Alpha’s online service into Excel.

To complement these data types, we are also building a new set of smart templates. The new smart templates will enable people to quickly get started with data types, and do things like track their nutritional information, research potential colleges, monitor stocks, learn chemistry, and much more.

We’ll be sharing more information about Wolfram Alpha data types as we get closer to general availability. In the interim, we hope you’ll join the Office Insiders program and give us your feedback!

Availability

Power BI data types are available in Excel for Windows (current channel) for all Microsoft 365/Office 365 subscribers that have a Power BI Pro service plan.

Power Query data types are currently rolling out and will be available in the coming weeks for all Microsoft 365/Office 365 subscribers, for use with all data sources supported in your SKU.

Wolfram Alpha data types are available for preview to Office Insiders.

Next steps

We hope you’ll bring all your questions and join us today for our data types event, where we will show you more examples and demos of the new things you can do in Excel with data types.

To learn more, also check out our Excel blog for a deeper dive on Power BI data types and Power Query data types.