Pivot table linking to non-existent data source

Hi Steve,

Thank you for reaching out and for the detailed information you provided.

Based on the behavior you described, this does not appear to be related to the operating system or licensing differences between Windows and Mac. Instead, it is likely that the workbook still contains a hidden or workbook-level reference to the original file.

In Excel, Refresh All does more than refresh the visible PivotTables. It can also refresh workbook connections, external data ranges, Power Query connections, PivotTable caches, and other stored connection metadata. This would explain why refreshing each PivotTable individually works, while Refresh All still attempts to access the original workbook. Microsoft documentation confirms that PivotTables can be connected to data in the same workbook, another workbook, or external data sources, and that Refresh All updates all PivotTables and connections in the workbook.

The original file reference may not appear in a standard formula search because it can be stored in workbook connections, queries, defined names, PivotTable cache information, or the workbook's data model rather than in worksheet formulas.

For reference, please review Microsoft's article on refreshing data connections in Excel: Refresh an external data connection in Excel | Microsoft Support

Please try the following checks:

1. Go to Data > Queries & Connections and review both the Queries and Connections tabs. Remove or update any items that still reference the original workbook.
2. Go to Data > Workbook Links (or Edit Links, if available) and break or update any remaining links to the old file.
3. Open Formulas > Name Manager and check whether any defined names contain references to the original workbook path.
4. For each PivotTable, select PivotTable Analyze > Change Data Source and verify that the source points to the table within the current workbook.
5. If the issue persists, create a new blank workbook, copy the data into it, and recreate the PivotTables from the local Excel table. This can remove lingering PivotTable cache data or connection metadata that may not be easily cleaned up.

As a temporary workaround, you can continue refreshing the PivotTables individually. However, the long-term solution is to identify and remove the remaining workbook-level connection or rebuild the PivotTables in a clean workbook.

I hope this information helps. Please let me know the results of the checks above, or if you receive any specific error message during Refresh All, and I will be happy to assist further.

Thank you and have a great day.

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".          

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.