Since 1987, Excel has let you input numbers, text, and formulas into a two-dimensional grid. Now Excel is going 3D: Data types let any cell contain a rich set of structured data that can be updated directly from a live data source. Data types can have all the information you need to write formulas, create charts, sort, filter, and make decisions based on data that Excel fetches for you. Forget having to manually go back to the original source and input everything into the grid.
Microsoft has been testing this functionality for a while, but in a limited capacity. In March 2018, Microsoft gave Excel its first two data types: Geography powered by Bing and Stocks powered by Refinitiv. In March 2020, Microsoft promised new data types for over 100 topics powered by Wolfram Alpha, spanning food, movies, places, chemistry, and even Pokémon. Several of those data types are finally available today, albeit in preview. But we knew those were coming. The bigger news today is that Microsoft is finally giving businesses what they really want: data types based on their own customer data.
“At the end of the day, Excel is a development environment,” Excel head of product Brian Jones told VentureBeat. “Right? People don’t really think of it, but you have the grid, you’ve got formulas, you’re essentially coding. It’s the world’s most popular programming language. And so this data type construct just gives you a lot more flexibility in terms of what you can go out and build.”
Native Excel integration
Power BI, Microsoft’s business analytics service, has over 250,000 customers. It follows that Microsoft would start there to help businesses access their data in Excel. Power BI data types are available today in Excel for all Microsoft 365 subscribers with a Power BI Pro service plan.
“What we’re really trying to do is make sure that we integrate Power BI natively into Excel,” Power BI CVP Arun Ulagaratchagan told VentureBeat. “Power BI has 30 million data models. A data model or a dataset is something connected to one of our 150+ connectors to different data sources. It might be Azure-based, it might be on-premise, it might be Salesforce, etc. And these datasets that run in the Power BI service are continuously refreshed on a schedule.”
Power BI supports up to 400GB data models (at about 10 to 20 times compression, that’s 4TB to 8TB of data in a single data model). These datasets can be IT-certified, and Microsoft Information Protection labels carry through. You can, for example, apply a security policy to mark them as highly confidential, full-time employee only, and so on.
When you build reports or dashboards in Power BI, it’s these datasets that they are talking to. And it’s these Power BI datasets that Microsoft has integrated directly into Excel.
If you don’t have Power BI, Microsoft still has you covered — you’ll just have to wait a little bit longer. Power Query data types are available today for Office Insiders, coming to all Microsoft 365 subscribers “in the next few weeks,” and will hit the Monthly Enterprise Channel “in the next couple of months.
Data types in action
Say you track your customers in Power BI. You can now import any customer data for analysis into Excel as a data type structured in the same way you or your company has defined a “customer.” Excel can now automatically access any data published in Power BI from corporate sources you have access to:
Your Power BI tables are available to you through the data types gallery. Once cells have been converted into linked data types, you can extract additional information to work with in your spreadsheets.
Microsoft also hopes data types will give you more confidence in the Excel data you are viewing. Not only do data types help you avoid copy/paste errors, but you can always check to see where the value in a cell is coming from.
For example, say you’ve imported the data type for Toronto into cell A1. You can access all the data type’s information just like you would traditionally write a formula in Excel. If you type A1.population into the cell B2, it would output 2,731,571. The cell B2, however, would not have a static value of 2,731,571. Because the value is coming from the Toronto dataset, you can always check the cell B2 to see the A1.population formula and thus its source.
Businesses without Power BI can still leverage the Power Query technology in Excel. You can manually connect to dozens of different types of data sources and specify that you want the data to be structured as a data type:
Power Query lets you create your own custom data types using any source of organization data available within the “Get Data” menu. You can set up an Excel data type based on anything from a basic CSV file, saved locally or hosted somewhere in the cloud, all the way to a database or online service.
Getting the latest data
Regardless of the source of the data type (Bing, Refinitiv, Wolfram Alpha, Power BI, or your own creation), you can always right-click the cell and hit Refresh Now to fetch the latest data. (Microsoft is also working on additional refresh options that can fetch the latest data at regular intervals).
The last thing a business wants (or any Excel user for that matter) is for data in spreadsheets to be overwritten with errors because the data source is unavailable. If the service is not working correctly, Excel does not update the data. All the latest data is stored in the Excel file and is only replaced if Excel successfully runs the refresh. If you can get to the data (you have the correction permissions and are online), Excel will pull down the latest values.
“If you try to refresh the data and Excel is unable to get to the data source (either because you are offline or the service is down), Excel will pop an error letting you know it could not get to the service, and the data will stay the same,” Jones confirmed.
Excel spreadsheets are about to get a lot more powerful — and a lot more complicated. If data types catch on, you’ll have to be aware not only of when a given Excel file was last modified, but also when it pulled the latest data for every data type therein.
You can’t solo security COVID-19 game security report: Learn the latest attack trends in gaming. Access here
