Skip to main content

Better Decision-Making with Improved Export to Excel and Connected PivotTables

Headshot of article author Ikechukwu Edeagu

Microsoft Excel is one of the most used data analysis tools across the globe and Power BI makes it easy for users to discover and connect to centralized, secure data shared with them directly within Microsoft Excel. This allows users to quickly access authoritative data in a tool they feel most comfortable working in, helps them quickly derive insights from their data, saves valuable time, and fosters an enhanced data culture.

Now I’m excited to share with you improvements made to the export to Excel experience and connected PivotTables in Excel for the web.

Improved Export to Excel Experience

For many organizations, Excel is the de facto exploratory data tool and no matter how simple or advanced a BI report is, users want to export data to Excel for analysis that supports the decision-making process. It is no wonder that one of the top 3 voted feature requests from the Power BI ideas community is improvements to the Export to Excel experience.

I’m pleased to announce today improvements to the Export to Excel experience for Power BI table and matrix visuals. Currently, when users export data to Excel from a table or matrix visual in the Power BI service, the exported data in Excel does not preserve the overall structure of the visual as the data output results in unpivoted rows and columns of data in Excel.

The new enhanced export experience includes a new, intuitive UI for the “Export data” dialog and a new export option, Data with current layout, which allows users to export data to Excel from a table or matrix visual while preserving the overall structure of that visual, number formatting, and expand collapse selections.

Animated GIF showing the new export experiences. It shows the new export dialog, the new data with current layout option, and demonstrates how an exported matrix looks when opened in Excel with this option.

We have started rolling out the export improvements and the public preview roll-out will complete in the coming weeks. With this availability, users would spend less time formatting data in Excel and focus on analyzing data that drives decision-making.

Enhanced Data Culture with Connected PivotTables in Excel for the web

Export to Excel is a great way to quickly work with Power BI data but if you need to keep your data updated then a connected experience is best because it is easy to refresh your data without leaving Excel. In connected Excel experiences, your data is automatically refreshed against authoritative data systems, and it is widely distributed through Power BI into Excel as datasets and organization data types. There are benefits to working with data in this connected way:

  • Microsoft Information Protection (MIP) labels in Power BI applies to your data in Excel so that your data remains secure.
  • Connected and Refreshed: Quickly connect to Power BI data from Excel and easily refresh that data without leaving Excel ensuring your analysis stays up to date.
  • Easy Distribution: Easily share your analysis with others while ensuring they can update the data from inside Excel when needed.

To help enhance connected experiences for our users, we are excited to announce today that you’ll be able to create connected reports and visualizations from Power BI datasets directly in a web browser from a blank Excel for the web workbook. The feature has started rolling out and would soon be available to all eligible users.

Screenshot showing the From Power BI Dataset option available in Excel for the web's Insert PivotTable ribbon.

This announcement is a follow-up to the GA of connected PivotTables refresh in Excel for the web that allowed users to refresh connected PivotTables in Excel for the web. Now you can start your data journey in Excel from a web browser by easily connecting to Power BI data shared with you while keeping that data refreshed.

The above capability adds to our great collection of connected Excel features such as Analyze in Excel and Organization Data Types which are now available to all web and semi-annual Office subscribers.

Screenshot of the From Power BI (Microsoft) option in Excel desktop's Insert PivotTable menu.

Screenshot of Excel's Data Types gallery with the Products and Organization data types highlighted which represent featured tables in Power BI datasets.

Next Steps

We are excited to see how organizations combine the versatility of Excel with the unified governance and security standards of Power BI to derive better insights from their data and foster an enhanced data culture. We love to hear from you on how we can make Power BI and Excel better together so let us know in the comments or submit an idea.