Microsoft Office Standard 2016 on a Windows 10 Enterprise 64-bit PC. Excel workbook has many sheets (tabs) which each contain a copy of the original pivot table. They’re all pivoted different ways, but linked to the same external source (a different Excel workbook) which is updated periodically. Nothing very fancy here, no Data Mart or whatever, just normal pivot tables all linked to a common source. “Refresh data when opening the file” is unchecked on all the pivot tables.
I want to refresh ONLY ONE of the pivot tables without necessarily refreshing all the others in the same workbook. I click in the one pivot table, click Analyze - Refresh, and am careful to select the option that says Refresh and NOT the option that says Refresh All. Still, every pivot table in the workbook refreshes itself when I do this.
All pivot tables after the first one were created by copying the original whole worksheet (tab) into a new worksheets (tabs) within the same workbook.
When you create a Pivot Table based upon a source range, or source connection, Excel creates a Pivot Cache which conceptually is a memory structure of the source data.
Copies of the Pivot Table, or Pivot Tables based upon the same source data will use the same Pivot Cache. And your refresh is actually a refresh of the Pivot Cache not the Pivot Table - if there are multiple PT’s using the same Pivot Cache then there is no way to individually refresh them.
However, you can break this sharing of the Pivot Cache so that each PT has its own cache and can be refreshed individually.
Method 2 at your link worked perfectly! And, I learned something. Thank you, kind sir.
I’ll c/p the steps that worked for me, slightly truncated, in case the link ever dies:
Ensure that there are at least two PivotTable reports based on the same cell range and that these reports share the same data cache.
Click a cell in the PivotTable report for which you want to unshare the data cache.
To start the PivotTable and PivotChart Wizard, press ALT+D+P.
On the Step 3 page of the wizard, click Back to return to the Step 2 page.
On the Step 2 page of the wizard, make sure that the same range of data on which you want to base the PivotTable report is selected, but that at least one fewer row is included in the selection.
For example, if the range is $A$1:$E$286, change the range to $A$1:$E$285.
Click Next.
On the Step 3 page of the wizard, make sure that Existing worksheet is selected and that the location is the same, and then click Finish.
The PivotTable report now has a different data cache but is based on a different data range.
Make sure that a cell in the PivotTable report for which you want to unshare the data cache is selected.
To start the PivotTable and PivotChart Wizard again, press ALT+D+P.
On the Step 3 page of the wizard, click Back to return to the Step 2 page.
On the Step 2 page of the wizard, change the range of data back to the original range.
For example, if the current range is $A$1:$E$285, change the range back to $A$1:$E$286.
Click Next.
On the Step 3 page of the wizard, make sure that Existing worksheet is selected and that the location is the same, and then click Finish.
The new PivotTable report is now based on the same data range as the other report, but has a different data cache.