Excel: Change the Data Source for Multiple PivotTables Without VBA
Question details
The user needs to change the source behind multiple Excel PivotTables without updating each PivotTable separately or writing VBA.
- Product
- Microsoft Excel PivotTables and shared data connections
- Device & OS
- not provided
- Scenario
- Several PivotTables use one shared PivotCache, connection, or query, and the underlying source URL or query needs to be replaced.
- Observed behavior
- Updating the shared connection or query can change the source for all connected PivotTables, while separate PivotCaches may require individual updates or another method.
Confirm that the PivotTables use the same connection or PivotCache by selecting each report and checking its connection settings. Record the replacement source URL, table, or query details before editing the shared connection.
Edit the shared Excel connection and refresh every PivotTable
Use this method when multiple PivotTables depend on the same connection, query, or PivotCache.
A shared connection can control the source used by several PivotTables. Editing that connection changes the source centrally, but the reports must be refreshed afterward to load the new data.
In Excel, open the Data tab and select Queries & Connections. In older Excel versions, choose Data > Connections.
In the Queries & Connections pane, right-click the shared query or connection and select Edit, Properties, or Connection Properties, depending on your Excel version.
In Power Query, update the Source step, such as the file path, URL, workbook, table, or database. For a connection, open the Definition or Connection Properties area and replace the source details.
Select Close & Load in Power Query, or click OK and Apply in the connection settings to save the new source.
Open the Data tab, select Refresh All, and verify that every related PivotTable now displays data from the replacement source.
Use Power Query when the PivotTables have separate caches
Use this fallback when Excel does not expose one shared connection for all PivotTables and you want to avoid VBA.
Switch from Excel PivotTables to a Lightweight WPS Office Workflow
WPS Office is a free, lightweight alternative for opening, editing, and sharing Excel workbooks. Its familiar spreadsheet interface and Microsoft Office format compatibility make migration straightforward, although Excel-specific shared connection behavior may require adjustment.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the home screen.
- 2. Open the Excel workbook: Select Open, choose the .xlsx or .xls workbook, and review the PivotTables and source data.
- 3. Update supported spreadsheet content: Use the Spreadsheet tools and Data options to edit source ranges or refresh supported PivotTable data, then check the reports for accurate results.

Frequently Asked Questions
How can I tell whether several Excel PivotTables share one connection?
Select a PivotTable and open PivotTable Analyze > Change Data Source or Data > Connections. Compare the connection or cache details with the other PivotTables. Matching connection information indicates that a shared source may be available.
Where is the Queries & Connections command in Excel?
Open the Data tab and select Queries & Connections. In some older Excel versions, use Data > Connections instead to view and edit workbook connections.
Will changing a shared connection update every PivotTable automatically?
It changes the source definition for all PivotTables that use that connection, but the displayed data may not change until you select Data > Refresh All. PivotTables with separate connections or caches may remain unchanged.
Can I change multiple PivotTable sources without using VBA if they have separate caches?
Excel may not provide one bulk-update command for separate caches. You can use Power Query to create a standardized source and reconnect the reports, or change each PivotTable through PivotTable Analyze > Change Data Source.




