Now Excel is going 3D

Data is at the core of Excel. Getting the data you need and quickly refreshing it is vital to everyday workflows. Microsoft provides you access to your company or organization information from Power BI as a data type in Excel. “Datatypes” allows any cell that contains a rich set of organized data that can be updated directly from a live data source. Data types contain all the information you need to write formulas, create charts, sort, filter, and make decisions based on what Excel brings to you. Forget about manually going back to the source and pitting down everything into the grid.

In March 2018, Microsoft bestowed Excel its first two data types: Geography powered by Bing and Stocks powered by Refinitiv. In March 2020, Microsoft committed to bringing new data types for over 100 topics powered by Wolfram Alpha. Today, the more significant news is that Microsoft is finally giving businesses what they want: data types based on their customer data.

Integrating Power BI natively into Excel

Power BI is Microsoft’s business analytical tool that has over 250,000 customers. It believes that Microsoft would start there to help businesses access their Excel data, hence integrating Power BI into Excel. Power BI data types are now available today in Excel for all Microsoft 365 subscribers with a Power BI Pro service plan.

Power BI supports up to 400GB of data sets. These data models can be IT-certified and Microsoft Information Protection labels. For example, you can apply a security policy to mark them as highly confidential.

When you create reports or dashboards in Power BI, it’s these Power BI datasets that Microsoft has integrated directly into Excel.

How do Datatypes work in Excel?

Say you monitor your customers in Power BI. You can import any customer data for analysis into Excel as a data type of how you or your company has identified a “customer.” Excel can now automatically access any information published in Power BI from corporate origin you have access to

Power BI tables are accessible to you through the data types gallery. Once cells have been transformed into linked data types, you can extract additional data to work within your spreadsheets.

Microsoft believes data types will give you more confidence in the Excel data you are viewing. Not only do data types help you keep away from copy/paste errors, but you can always monitor to see where the value in a cell is coming from.

Getting the latest data

If you’re going to work with huge datasets, those large bits should be stored using an external source. Fetching the latest data from the library, data stream, or website will simply require you to refresh the data using an updated feature. This helps refresh data at certain intervals.

The last thing a business needs is for data in spreadsheets to be overwritten with errors because the data source is unavailable. If the tool is not working correctly, Excel does not update the data. All the latest information is stored in the Excel file and is only replaced if Excel successfully runs the Refresh. Excel will pull down the latest information to get to the data (you have the correct permissions and are online).

Excel spreadsheets with Power BI are about to get a lot more powerful — and a bit more complicated. If data types are held on, you’ll have to be conscious of when a given Excel file was last modified and when it pulled the latest data for every data type therein.

Nov 09, 2020 | Upcoming Updates