How to Use Power BI in Excel

Using Power BI and Excel together once meant exporting data from one to the other, creating a static, disconnected mess. Now, you can seamlessly connect to Power BI's powerful datasets and reports directly within Excel, bringing live, interactive analysis into your familiar spreadsheet environment. This guide will walk you through exactly how to connect to Power BI data from Excel, analyze it with PivotTables, and embed live dashboards right into your worksheets.

Why Use Power BI directly in Excel?

Connecting these two tools solves a long-standing headache for anyone who works with data. Instead of choosing between the structured governance of Power BI and the ad-hoc flexibility of Excel, you now get the best of both. This integration allows you to maintain a "single source of truth" while still empowering users to explore insights in their preferred application.

Maintain Data Integrity: When everyone connects their Excel files to the same, centrally managed Power BI dataset, you eliminate the risk of different people using outdated or inconsistent spreadsheets. The data definitions, calculations, and relationships are managed in Power BI, ensuring every analysis starts from the same governed foundation.
Enjoy Familiarity and Flexibility: Millions of people know and love Excel for its feel and grid-like structure. This integration lets them continue working in that familiar space while leveraging the powerful data models and DAX calculations created in Power BI. You can build complex PivotTables or use Excel functions like XLOOKUP on data sourced directly from Power BI.
Access Real-Time Data: A report built in Excel connected to Power BI is no longer a static snapshot. It contains a live connection to the dataset, which can be scheduled to refresh automatically in the Power BI service. A simple click of the "Refresh" button in Excel ensures your analysis always reflects the most current information.
Enrich Static Reports: You can embed fully interactive Power BI visuals - slicers and all - into your Excel worksheets. This is perfect for enhancing management reports or financial summaries, replacing static screenshots with dynamic charts that stakeholders can filter and explore themselves.

Getting Started: Your Pre-Flight Checklist

Before you get started, ensure you have the required setup. The process is straightforward, but you need a few things in place for the connection to work smoothly.

Licensing: You need a Power BI Pro or a Premium Per User (PPU) license to publish reports and datasets to the Power BI service that Excel connects to. Your organization must also be using a Microsoft 365 E5 license or your Excel will not have this feature available.
Excel Version: This functionality is available in modern versions of Microsoft 365 for Windows and Mac, as well as Excel for the Web. Legacy desktop versions of Excel won't have the necessary data connectors.
Permissions: The creator of the Power BI dataset must grant you "Build" permissions. This allows you to connect to the dataset and create new content from it, like an Excel PivotTable, without letting you edit the original source.

How to Connect Excel to a Power BI Dataset

The primary way to use Power BI in Excel is by connecting to an existing dataset. Think of a Power BI dataset as a curated package of data that has already been cleaned, modeled, and enriched with business calculations (called measures). Connecting to it means you don't have to start from scratch.

Follow these steps to establish the connection:

Step 1: Open the Power BI ConnectorIn Excel, navigate to the Ribbon and click on the Data tab. From there, select Get Data > From Power Platform > From Power BI.

Step 2: Select Your DatasetA pane will appear on the right side of your screen listing all the Power BI datasets you have access to. You will see their names, their owners, and the workspace they belong to.Look for datasets that have been officially endorsed by your organization – they will be marked with a Promoted or Certified label. Certified datasets are the gold standard, as they signal that the data has been vetted for accuracy and adheres to company standards. This is your cue to trust the data completely.

Step 3: Insert a PivotTable or TableAfter selecting a dataset from the list, you have two main options at the bottom of the pane:

Insert PivotTable: This is the most common choice. It creates a new PivotTable that is directly connected to the Power BI model.
Insert Table: This option allows you to create a flat Excel table from the Power BI data. It's less common, as it is limited to a smaller volume of data and removes the layered analytical power of a PivotTable.

For this example, choose Insert PivotTable.Once you click it, a new worksheet with an empty PivotTable will be created. You're now ready to start analyzing your data.

Building Reports with PivotTables Connected to Power BI

With your PivotTable connected to Power BI, your analysis process is almost identical to using a standard PivotTable, but with a few powerful differences.

Familiar Drag-and-Drop Interface

On the right, you'll see the PivotTable Fields list, which is now populated with the tables, columns, and measures from your Power BI dataset. All the relationships between tables (e.g., how sales relate to products and customers) are already defined in the model, so you can mix and match fields seamlessly.

For example, take a typical sales analysis scenario:

Drag the pre-built DAX measure [Total Sales] from your Sales table into the Values area.
Drag [Product Category] from your Products table into the Rows area.
Drag [Country] from your Customers table into the Filters area.

Instantly, you get a clean summary of sales by product category, and the DAX measure ensures the calculation is performed correctly according to the business logic defined in Power BI.

Leveraging Pre-Built DAX Measures

One of the biggest advantages here is the ability to use the sophisticated DAX (Data Analysis Expressions) measures that were already built in Power BI Desktop. The formulas for key metrics like Total Sales, Year-over-Year Growth, or Profit Margin are stored centrally in the dataset.

This means you don't need to write complex formulas in Excel cells. Power BI handles the heavy lifting, ensuring everyone in the organization calculates key business metrics in the exact same way. These measures are often symbolized by a small calculator icon in the Fields list.

Refreshing Your Data

The best part of this setup is that your report isn't static. When the underlying Power BI dataset updates with new information (e.g., daily sales are loaded), you can easily get the latest data in your spreadsheet.

Simply navigate to the Data tab and click Refresh All. Excel will query the Power BI service and update your PivotTable with the freshest numbers available. This turns a manual, error-prone reporting process into an efficient, reliable one.

Embedding Live Power BI Reports into Excel

Sometimes, a PivotTable isn't enough. You might want to include a rich, interactive visual from an existing Power BI report, such as a map or a detailed decomposition tree. This is possible with the Power BI add-in for Excel.

Step 1: Open the Power BI Add-inNavigate to the Insert tab in the Excel ribbon. Near the center, you should see an icon for Power BI. If you don't, you may need to add it from the Office Store via Insert > Get Add-ins.

Step 2: Select and Paste a Report URLAfter clicking the icon, a Power BI panel will open. Here, an easy method is to find the report in the Power BI service online that you want a live connection to inside Excel, and copy/paste its workspace URL.

Step 3: Interact with the Embedded ReportOnce you insert the report, a live, interactive window appears directly on your Excel grid. You can now resize it and place it alongside your spreadsheet data.All the interactivity of the original report is preserved:

Slicers and Filters: Use the on-screen controls to filter the data.
Cross-Filtering: Click on a bar in one chart, and it will filter the other visuals in the embedded report.
Drill-down: If drill-down capabilities were built into the visuals, you can use them inside Excel too.

Use Cases for Embedded Reports

This feature is a game-changer for adding context to traditional reports. You could place a live sales performance dashboard next to your quarterly finance commentary or embed a customer demographics map into your marketing planning spreadsheet.

Final Thoughts

Integrating Power BI with Excel bridges the gap between governed, enterprise-ready analytics and the fast, flexible analysis everyone loves in spreadsheets. By connecting to a central Power BI dataset, you ensure your work is always based on trusted, up-to-date information while retaining the creative freedom Excel offers.

This move to make powerful data analysis more accessible and user-friendly is what we are passionate about. That's why we created Graphed, which simplifies this process even further. Instead of needing to build complex data models in Power BI, we let you connect all your marketing and sales platforms - from Google Analytics to Salesforce - and create dashboards in seconds just by describing what you want to see in simple, plain English - no learning curve required.