Fix Excel PivotTable Cannot Open Pivot Source File on Refresh
Question details
The user needs to refresh an Excel PivotTable without opening its external source workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A PivotTable and its source workbook are stored in the same folder, but the source workbook is closed when the PivotTable is refreshed.
- Observed behavior
- Excel displays a Cannot open pivot source file error, indicating that the source reference or connection cannot be resolved or accessed.
Locate the source workbook and confirm that you can open it from its current folder. Check its exact filename, source worksheet or table name, and whether any network or cloud location is available.
Check the PivotTable source reference and file access
Start here if the source workbook has been moved, renamed, or stored in a location Excel cannot access.
Keeping both workbooks in the same folder does not guarantee that Excel can resolve the PivotTable source. A valid full path can fix a broken reference, but closed-workbook refresh also depends on the source and connection type.
Select a cell inside the PivotTable, then choose PivotTable Analyze > Change Data Source. Inspect the Table/Range reference. For a connection-based PivotTable, open Data > Queries & Connections and inspect the relevant connection instead.
Use your file manager to navigate to the source workbook and open it. Confirm that the filename, worksheet or table name, and referenced range still exist. If the file is on a network share or cloud drive, reconnect or make it locally available as needed.
In PivotTable Analyze > Change Data Source, replace an outdated reference with the correct source. For a range-based external source, use a fully qualified reference such as 'C:\Reports\[Source.xlsx]Data'!$A$1:$D$500, adjusted to your actual location and range. For a connection-based source, update the file location in its connection settings.
Save the source workbook, close it, then return to the destination workbook. Right-click the PivotTable and select Refresh. If the error continues despite a valid, accessible source, use Power Query or a local source table.
Use Power Query to import data from the closed source workbook
Use this approach when you need repeatable refreshes from an external workbook without keeping that workbook open.
Move the source data into the PivotTable workbook
Use a local table when you do not need an ongoing external connection or want to remove the source-file dependency.
Work with Local PivotTable Data in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easy transition. WPS Spreadsheet can help you analyze local data with PivotTables, but it does not repair Excel-specific external connections or Power Query refresh errors.
Frequently Asked Questions
Why does the error appear when both workbooks are in the same folder?
The PivotTable may still reference an old path, a renamed worksheet, or an unavailable connection. Select the PivotTable and open PivotTable Analyze > Change Data Source to inspect its reference; use Data > Queries & Connections for a connection-based source.
Can an Excel PivotTable refresh while its source workbook is closed?
It depends on the source and connection type. An accessible file path alone does not guarantee that a direct external-range source will refresh. Power Query can read saved data from a closed workbook: use Data > Get Data > From File > From Excel Workbook where available, load a local table, and build the PivotTable from it.
Why does refreshing work only after I open the source workbook?
Opening the source may allow Excel to resolve a reference that it cannot use when the workbook is closed. Check the source under PivotTable Analyze > Change Data Source, verify the full file location, and consider importing the data with Power Query if closed-workbook refresh remains unreliable.
Will copying the source data into the destination workbook keep it updated automatically?
No. Paste Special > Values creates a snapshot. Update the local table manually before refreshing the PivotTable, or use a Power Query import if you need a repeatable connection to the source file.
Can WPS Office fix this Excel source-file error?
WPS Office does not repair Excel-specific source references or connection settings. WPS Spreadsheet is an alternative for working with compatible Excel files and creating PivotTables from local data; verify advanced external-connection behavior before migrating the workbook.
