How to Use Power BI Together with Excel

Microsoft Excel and Power BI offer several methods of working together. Users can connect Excel to Power BI data sets to work with pre-made data models, or they can connect Power BI to Excel workbooks to create interactive reports, dashboards, or apps that automatically update.

Power BI and Excel support a level of integration such as importing Excel into Power BI and connecting Excel to Power BI data models.

When using Excel and Power BI together there is a decent amount of overlap between use cases. Both platforms even support Power Query, a tool from Microsoft that allows you to automate the transformation of data to prep it for reporting. We’ll explain the commonalities, when to use each one and how to use them together.

Let’s roll!

The Difference and Similarity Between Power BI and Excel

Power BI and Excel are both powerful tools developed by Microsoft, used for data analysis and visualization. Power BI in many ways builds on the visualization and data exploration capabilities of Excel in a more modern, easily sharable platform. Here are some of the similarities and differences to keep in mind when determining which platform is right for your project.

Similarities between Excel and Power BI

  • Data Analysis and Visualization – Both tools can be used to analyze data and create visualizations like charts and graphs.
  • Integration – Power BI and Excel can integrate with each other; Excel data can be imported into Power BI for more advanced analytics, and Power BI reports can be embedded in Excel.
  • Microsoft Ecosystem – Being part of the Microsoft suite, they can integrate well with other Microsoft products like SharePoint, Teams, and Office 365.

Differences between Excel and Power BI

  • Complexity and Capability – Excel is primarily a spreadsheet tool with extensive capabilities for calculations, data manipulation, and basic to intermediate data visualization. Power BI, on the other hand, is a more powerful business intelligence tool designed for creating complex data models, advanced data visualization, and sharing insights across an organization.
  • Data Handling – Excel is suitable for handling smaller datasets, whereas Power BI is designed to work efficiently with large datasets and can connect to various data sources directly.
  • Real-time Data Analysis – Power BI offers more advanced features for real-time data processing and dashboard updates, which Excel lacks.
  • Collaboration and Sharing – Power BI provides more robust options for sharing reports and dashboards with others, allowing for better collaboration within teams and organizations. Excel is more individually focused, though shared workbooks can also facilitate collaboration to some extent.

In general, Excel is more suited for individual use with smaller datasets and simpler models. Power BI is designed for handling complex data analysis, sharing reports across teams, and dealing with large datasets in a corporate or enterprise environment.

Power Query is Available in Excel and Power BI

Power Query is Microsoft’s tool for preparing and transforming data. It allows you to connect to or import Excel files, folders of files, numerous other file formats, connect to SQL databases and more. Once connected, you apply data transformation steps that take your data from a raw format to a tabular format that can be easily presented in pivot tables, charts, graphs or other visuals.

Screenshot of the Power Query interface available in both Excel and Power BI

Microsoft has recently expanded the use of a Power Query style interface for their data engineering tools, Data Flows Gen2 and machine learning tool, Data Wrangler.

This allows people to learn Power Query once and utilize it across several disciplines and many different use cases.

Creating reports in Power BI from Excel Data

To create a Power BI Report using Excel data, first download Power BI Desktop. Then open it and go to the data section and choose Get Data or Excel Workbook and select the file you want to work with. Once you load the file, add a visuals to the report page, and assign fields from the data to the visual similar to working with a pivot table.

Here’s how in more detail.

Power BI Desktop has multiple ways to perform the same action. The fastest way to import an Excel workbook is using the “Excel Workbook” button from the Home section of the Power BI Ribbon at the top of the screen.

The Import Excel Workbook and Get Data buttons in Power BI to import Excel workbooks

Alternatively, you can choose to Get Data which gives you an extensive number of options not limiting yourself to only being able to import Excel workbooks. You can choose to connect to hundreds of different data sources.

Get data options have hundreds of possibilities beyond connecting only to Excel workbooks

After choosing a file you can either Load it which makes it available for reporting, or you can continue to transform it which launches the Power Query Editor where you can adjust the data, filter it, remove null rows, blend it with other data etc.

Example of excel data being imported into Power BI to build visuals and reports off of

Once the data is loaded, you can assign it to visuals and organize multiple visuals into report pages.

Creating Visuals in Power BI from Excel Data

Power BI works mostly with a drag and drop interface. Visuals are added to the report canvas or blank page at the left of the screen by first selection a visual, then dragging and dropping columns or fields of available data into the available fields for a specific visuals similar to putting together a pivot table or a pivot chart.

Screenshot of the Power BI Desktop interface with notes about how to add excel data to visuals on a report page

The example above highlights a few different steps that you can use to setup a visual.

  1. A list of different visual types including charts, graphs, and tables. Click this to add a visual to your report canvas.
  2. A list of all available fields or columns on a data set that’s been connected to or imported. Drag and drop these to #3
  3. Blank fields that have fields assigned to them from a data set to generate a visaul.

As you drag and drop fields into different sections the visual begins to generate. You can often add multiple fields to a single box allowing people to drill into and explore data.

Using Power BI in Excel

Power BI and Excel are two different platforms with no way to directly place a Power BI visual directly into Excel. However, you can connect Excel to a Power BI data set allowing you to build visuals and spreadsheets in Excel based on data that’s based in Power BI.

This method has several advantages, the key one being that while Power BI is great at creating interactive reports full of charts and graphs it’s pretty bad at re-creating highly formatted spreadsheets that people are often used to looking at.

By connecting

Importing data from Power BI to Excel

To import data from Power BI to Excel, you have to first publish a report from Power BI to PowerBI.com. You can then use Excel to “Get Data” from the Power Platform and select “From Power BI”. You will then have the option to import a table of data or to import the data into Power Pivot.

For this method to work, you will first need to make sure that the semantic model, or data set is available on PowerBI.com and you have access to the Workspace. You’ll also need to ensure that you are logged into Excel using the same account that has access to the Power BI report.

Go to “Data”, “Get Data”, “From Power Platform”, “From Power BI” to get started.

Screenshot of getting data from Power BI and importing it into an Excel workbook

A list will appear on the right side of the screen with different datasets that you have access to. You can choose to use them to either insert a pivot table or insert a table of the data.

A list of Power BI datasets available to import into an Excel workbook when connected to the Power BI Service

In our example, we chose to Insert a Table. When doing so, an editor appears that lets you choose specific tables, or specific fields.

Screenshot of selecting fields to import into Excel from a Power BI data set using the create table wizard

After pressing OK the data will be loaded from Power BI into an Excel spreadsheet. There are some limitations in data size and usability using this method. Standard Excel sheets only handle about 1 million rows as a maximum and performance can be so slow that the limit is much less than that.

When importing large sets of data into Excel, we recommend using Power Pivot as it uses the same data engine as Power Query and Power BI which is significantly more efficient than importing a flat table.

Conclusion

Power BI and Excel are two different platforms with a lot of overlapping features. Power BI is targeted at business and enterprise users while Excel is universal across business small and large. By knowing when to use each one you can use them together to leverage the best capabilities of both platforms.

This can either mean importing Excel data into Power BI or connecting Excel to an already created Power BI semantic model. When working with either be aware of some of the platform limitations that exist on each one, for example you can only import so much data into an Excel spreadsheet and Power BI won’t automatically update data without a scheduled refresh when connected to an Excel workbook.

Scroll to Top