Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Power BI, Fabric, SQL, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
When you view reports in the Power BI service (https://app.powerbi.com
), you might want to further enrich the report data with additional assumptions, perform what-if analysis, or validate the numbers in a specific Power BI visual or an entire Power BI report.
In this article, you learn how to create Excel workbooks containing connected Power BI data that you can refresh directly inside the workbook. That way, you can focus on your analysis in Excel and be confident that you are working with up-to-date data.
Power BI provides you with options to ensure that you can extract the right granularity of data depending on how you want to analyze that data in Excel, either with Excel PivotTables or Excel tables. In the Power BI service, use the following two features to create an Excel workbook:
With Analyze in Excel, you can create an Excel workbook containing the entire semantic model for a specific Power BI report and analyze that semantic model in Excel using PivotTables, Pivot Charts, and other Excel features.
In the Power BI service, you can use any of the following Analyze in Excel entry points to create an Excel workbook:
Open a Power BI report. Select Export > Analyze in Excel from the top ribbon.
Go to the workspace containing your Power BI semantic model or report, select More options (...) next to the semantic model or report name and select Analyze in Excel.
Select a semantic model in a Power BI workspace. In the Semantic model details page, select Analyze in Excel on the menu bar.
After you select Analyze in Excel, Power BI generates an Excel workbook and saves it to your OneDrive SharePoint account. It has the same name as the Power BI report, so you can open the workbook directly in Excel for the web.
Note
If you don't have a OneDrive SharePoint account, Power BI downloads the Excel workbook to your local computer.
When you choose Open in Excel for the web, your Excel workbook opens in a separate browser tab. To enable the Power BI query in Excel, select Yes on the Query and Refresh Data dialog.
After you select Yes in the dialog, you can see the tables and measures from your Power BI semantic model in the PivotTable Fields. You can start building your PivotTable reports in Excel.
If you want to work with your data in the Excel Desktop app, select the Editing button in the ribbon and select Open in Desktop app.
While viewing a Power BI visual, you may want to further explore the visual data in Excel and keep the data connected. You can export a table of refreshable data from a Power BI visual to Excel:
Choose a visual in a Power BI report, select More options (…).
On the Options menu, select Export data. You have different options to select what type of data you want to export to Excel.
Select the Summarized data card, and choose the .xslx (Excel) with live connection (500,000 row max) option under File format.
After you select Export, Power BI downloads an Excel workbook containing the live Power BI data to your computer.
The first time you open the Excel workbook, it might open in read-only mode until you select Enable Editing, depending on your Protected View settings in Office.
Depending on your Trusted document settings, you might also need to select Enable Content to load the Power BI data to the Excel grid.
The data from the Power BI visual is now visible as an Excel table that can be refreshed against Power BI.
Note
The ExportHeaders worksheet in the Excel workbook contains details about the filters applied to the Power BI visual that the data was exported from.
In addition, you can use the OneLake Data Hub's Table Preview to export an Excel workbook with a live connection.
You can use the Power BI Excel add-in to insert connected PivotTables and connected query tables starting from Excel. This helps you quickly find and insert refreshable Power BI data into your new or existing workbooks without leaving Excel.
Note
To use the Power BI Excel Add-in, ensure that in Excel Trust Center the following settings are not checked:
Note
The connected tables feature is now available in the Microsoft 365 Current Channel. Connected tables will be available to all Monthly Enterprise Channel and Semi-Annual Enterprise Channel customers in their next regularly scheduled Microsoft 365 update. The connected tables feature is available for everyone in Excel for the Web. The add-in is visible in Excel for users with a Power BI Pro license.
You can open the Excel Add-in from the Excel ribbon.
Starting on the Insert ribbon:
Starting on the Data ribbon:
The Power BI add-in Datasets Pane opens in Excel and provides the following capabilities:
You can use Excel's refresh capabilities to update the data in the Excel workbook for your connected PivotTable or connected table.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Power BI, Fabric, SQL, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayTraining
Module
Transcend data analysis together with Power BI and Excel - Training
This module introduces potential ways that people can use Excel and Power BI together. Power BI is a great tool for telling a story through visuals; occasionally, it's beneficial to use Excel for analysis. Excel can make a connection to a Power BI dataset, enabling you to analyze the data by using PivotTables.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.