How to Make an Excel PivotTable Reference the Current Workbook
Question details
The user needs a copied PivotTable to retrieve data from a table in the current destination workbook instead of maintaining a link to the original source workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying a worksheet containing a PivotTable from one workbook to another, expecting it to automatically link to an identically named table in the new workbook.
- Observed behavior
- The copied PivotTable's pivot cache continues to reference the external original workbook and does not automatically switch to the local table.
Ensure both the original and destination workbooks are open, and verify that the destination workbook contains the local data table you intend to use as the new source.
Manually Change the PivotTable Data Source
The most reliable method is to directly update the PivotTable's data source settings to point to the local table in the destination workbook.
When a PivotTable is copied, its cache remembers the exact file path of the original source. You must explicitly tell the PivotTable to look at the local worksheet instead.
Open the destination workbook and click anywhere inside the copied PivotTable to display the PivotTable Analyze (or Options) tab on the ribbon.
Click on 'Change Data Source' in the Data group.
In the Table/Range field, delete the external reference. Highlight the local data table (e.g., SalesData) in your current workbook, or manually type the local table name.
Click 'OK'. Finally, click 'Refresh' to ensure the PivotTable updates based on the newly linked local data.
Recreate the PivotTable Locally
To completely avoid pivot cache conflicts, you can build a new PivotTable directly from the local table in the new workbook.
Use Power Query to Load Local Data
For repeatable deployments, Power Query can automatically load the correct local data before the PivotTables are generated.
Manage PivotTables Easily with WPS Spreadsheet
WPS Spreadsheet provides a highly compatible environment for Excel files. You can effortlessly update PivotTable data sources, manage pivot caches, and conduct comprehensive data analysis without formatting loss.
- 1. Open the workbook: Launch WPS Spreadsheet and open the destination workbook containing the copied PivotTable.
- 2. Access PivotTable tools: Click anywhere inside the PivotTable. The 'PivotTable' tab will automatically appear on the top ribbon.
- 3. Change data source: Click the 'Change Data Source' button. Clear the old external path in the dialog box.
- 4. Select local range: Drag to select the new data table within your current workbook and click 'OK' to finalize the update.
Frequently Asked Questions
Why does my copied PivotTable still show data from the old workbook?
When you copy a PivotTable worksheet to a new workbook, it retains its original 'Pivot Cache'. This cache points to the exact file path of the source workbook to preserve data integrity, and it does not automatically switch to a local table, even if it has the same name.
Does clicking 'Refresh' fix the external reference issue?
No. Clicking 'Refresh' simply prompts Excel to pull the latest data from the linked source. Since the source is still the original external workbook, it will not update to reflect the new local data unless you manually change the data source first.
How can I check if my PivotTable is linked to an external workbook?
You can go to the 'Data' tab and look for the 'Edit Links' button. If it is clickable, your workbook contains external references. Alternatively, clicking 'Change Data Source' on the PivotTable will reveal the current file path in the Table/Range box.
Can I use VBA to automatically update the PivotTable source to the current workbook?
Yes. If you regularly deploy copied sheets, you can use a VBA macro to loop through all PivotTables in the active workbook and redefine their 'SourceData' property to point to the local destination table.
