Pivot Table Issues

How to Add Another Data Source to Excel PivotTables and Charts

Olivia Miller Oct 12, 2026 868 views

Question details

The user wants to know how to connect their Excel PivotTables and charts to additional external data sources, such as Power BI datasets or multiple sources combined via Power Query.

How to Add Another Data Source to Excel PivotTables and Charts

Product: Microsoft Excel / Power BI
Device & OS: not provided
Scenario: Expanding data analysis capabilities by integrating multiple or external data sources into a single PivotTable.
Observed behavior: Need precise instructions on appending, merging, or connecting external datasets to use as the foundation for Excel PivotTables and charts.

Before you start

Ensure you have an active Power BI license, the necessary permissions to access external datasets, and that you are signed into Excel with the account associated with your Power BI workspace.

Solution 1Recommended

Create a PivotTable Directly from a Power BI Dataset

Use this method if your data is already hosted in Power BI and you want to directly connect your Excel PivotTable to it.

Connecting Excel directly to a Power BI dataset is the most seamless way to utilize cloud-hosted data for your PivotTables. This requires a proper Microsoft 365 subscription and Power BI permissions.

Open Excel and Access Insert Menu

Launch Microsoft Excel and open a new or existing workbook. Navigate to the 'Insert' tab on the top ribbon.

Select Power BI as the Data Source

Click on 'PivotTable' and choose the 'From Power BI' option from the dropdown menu.

Choose Your Dataset

A side pane will appear on the right displaying all Power BI datasets you have permission to access. Select the desired dataset to load it directly into your PivotTable.

Create a PivotTable Directly from a Power BI Dataset

Data Connection: Once connected, your PivotTable will remain linked to the Power BI dataset, allowing you to easily refresh and fetch the latest data from the cloud.

Solution 2

Merge Data Sources Using Power Query Before Pivoting

Ideal when you have multiple separate data sources that need to be combined into one unified dataset before creating a PivotTable.

Solution 3

Utilize Power BI Dataflows for Cloud Transformations

Best for enterprise scenarios where you need reusable cloud-based data transformations across multiple Excel reports.

Free Microsoft Office alternative

Experience Seamless Data Analysis with WPS Office

While advanced Power BI integrations require Microsoft's enterprise ecosystem, WPS Office provides a lightweight, highly compatible, and completely free alternative for everyday data consolidation and PivotTable reporting.

1. Download and Install: Visit the official WPS website to download the free installation package for your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open any existing .xlsx files without formatting loss.
3. Create PivotTables: Navigate to the 'Insert' tab and click 'PivotTable' to start summarizing your data immediately.

Fully compatible with Microsoft Excel formats (.xlsx, .csv)Built-in advanced PivotTable and charting tools for robust data analysisLightweight architecture for faster startup and performanceEasily consolidate data from multiple worksheets without complex queriesFamiliar user interface for a seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Can I connect a PivotTable to multiple Excel sheets without using Power Query?

Yes, you can use the Data Consolidation feature or the legacy PivotTable and PivotChart Wizard (accessible by pressing Alt + D, then P) to combine multiple ranges from different sheets, though it provides less flexibility than Power Query.

Why can't I see the 'From Power BI' option in Excel?

This feature requires a specific Microsoft 365 enterprise or business subscription, an active Power BI license, and you must be signed into Excel with the same Microsoft account associated with your Power BI workspace.

Does my combined Power Query data automatically update in the PivotTable?

It does not update automatically in real-time. You need to manually refresh the PivotTable. Simply right-click anywhere inside the PivotTable and select 'Refresh', or go to the Data tab and click 'Refresh All' to pull the latest data from the combined sources.