Unshare a data cache between PivotTable reports

By default, PivotTable reports that are based on the same data source — a cell range in a worksheet or a data connection — share a data cache, but you can unshare this data cache by using several methods.

What do you want to do?

Learn more about the PivotTable data cache

The data cache of a PivotTable report is an area of internal memory on your computer that is used by Microsoft Office Excel to store the data for the report. To help improve performance and reduce the size of your workbook, Excel automatically shares the PivotTable data cache between two or more PivotTable reports that are based on the same cell range or data connection. If the cell range or data connection for two or more PivotTable reports is different, the data cache cannot be shared between those reports.

However, there may be times when you do not want to share a data cache between two or more PivotTable reports that are based on the same data source. For example:

You do not want calculated fields and items to be displayed in all the PivotTable reports.

You do not want fields to be grouped in the same way in all the PivotTable reports.

You do not want all the PivotTable reports to refresh at the same time.

You want to use a feature that is not supported when you use a shared data cache, such as retrieving data in a report for a selected item or items when you apply a report filter.

Note: The data cache for a PivotTable report that is connected to an Online Analytical Processing (OLAP) data source cannot be shared because it is used in a different way than non-OLAP data sources.