With new tools being developed, some thought the best days of Excel were over. However, Microsoft has been regularly adding new updates to its legendary spreadsheet application. This still makes it a go-to data analytics solution in most companies.
In its latest update to Excel, Microsoft added new data types, including additional public data and custom business data types. The latter allows users to turn their own data into a data type.
Last year, Microsoft added dynamic arrays, array formulas, stock and geography data types, which made Excel cells more “intelligent,” so to speak. The latest update added a bunch of new data types, including ZIP codes, information on food types and nutrients, and even movies.
Users can also create their own data types, which are directly connected to a live data source using Power Query. This way Excel spreadsheets are no longer static. Another advantage of connecting to a live data source is to reduce the likelihood of making manual errors.
“Up to this point, the Excel grid has been flat: it’s two dimensional… 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 Brian Jones, Group Program Manager for Excel.
Closer integration between Excel and Power BI
Microsoft is using Power BI as a connector to bring data sources into Excel. Now businesses will be able to connect to a variety of their own data sources and use them as data types. Power BI allows for a shared, controlled, and secure access to data.
Connecting Excel to Power BI datasets links the two in a new way, effectively turning Excel into a live spreadsheet application.
Excel and Power BI have always been closely integrated. In the past, it’s been mostly a one-way integration with Excel serving as a source for Power BI. With the new update, however, Power BI integrates back into Excel, allowing Excel users to leverage the BI tool.
The new Power BI data types will be available in Excel for all Microsoft 365 / Office 365 subscribers that also have a Power BI Pro service plan.