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

• Unshare the data cache between PivotTable reports

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.

Top of Page

Unshare the data cache between PivotTable reports

There are several ways to unshare a data cache, including the following:

• Use the PivotTable and PivotChart Wizard to create a new PivotTable report that is based on the same cell range as another report without sharing the data cache.

• Unshare the data cache between PivotTable reports that are based on a cell range by temporarily redefining the data range to force Excel to unshare the data cache.

• Unshare the data cache between two or more PivotTable reports that are based on the same data connection by creating a unique data connection for each PivotTable report in the workbook.

Tip

To see how many data caches there are in a workbook, open the Immediate window of the Visual Basic Editor by pressing ALT+F11 and pressing CTRL+G, and then type the following:

?ActiveWorkbook.PivotCaches.Count

Create a PivotTable report that is based on the same cell range as another report without sharing the data cache

1. Ensure that there is an existing PivotTable report that is based on the same range that you want to use for the new PivotTable report.

2. Click any blank cell in the worksheet outside the PivotTable report.

3. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

   Tip

   To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:

   1. Click the arrow next to the toolbar, and then click More Commands.

   2. Under Choose commands from, select All Commands.

   3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

4. On the Step 1 page of the wizard, click Microsoft Office Excel list or database, and then click Next.

5. On the Step 2 page of the wizard, select the range of data on which you want to base the new PivotTable report, and then click Next.

6. When the PivotTable and PivotChart Wizard displays a message asking if you want to share the data cache, click No.

7. On the Step 3 page of the wizard, select a location for the new PivotTable report, and then click Finish.

Unshare the data cache between PivotTable reports that are based on a cell range

1. Ensure that there are at least two PivotTable reports based on the same cell range and that these reports share the same data cache.

2. Click a cell in the PivotTable report for which you want to unshare the data cache.

3. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

   Tip

   To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:

   1. Click the arrow next to the toolbar, and then click More Commands.

   2. Under Choose commands from, select All Commands.

   3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

4. On the Step 3 page of the wizard, click Back to return to the Step 2 page.

5. 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.

6. Click Next.

7. 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.

8. Make sure that a cell in the PivotTable report for which you want to unshare the data cache is selected.

9. To start the PivotTable and PivotChart Wizard again, press ALT+D+P.

10. On the Step 3 page of the wizard, click Back to return to the Step 2 page.

11. 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.

12. Click Next.

13. 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.

Unshare the data cache of two or more PivotTable reports that are based on the same data connection

1. Make sure that there are at least two PivotTable reports that are based on the same data connection and that these reports share the same data cache.

   Confirm that the same data connection is used between PivotTable reports

   1. On the Data tab, in the Connections group, click Connections.

   2. In the Workbook Connections dialog box, select the PivotTable report connection.

   3. Under Locations where connections are used in this workbook, click the link that displays the text Click here to see where the selected connections are used.

      The PivotTable reports that use this data connection are displayed.

2. Make sure that there is a connection file for the data connection on your computer or on the network.

   Create a connection file for a data connection in a workbook

   1. On the Data tab, in the Connections group, click Connections.

   2. In the Workbook Connections dialog box, select the PivotTable report connection.

   3. Click Properties.

   4. In the Connection Properties dialog box, click the Definition tab, and then click Export Connection File.

   5. In the File Save dialog box, save the current connection information as an .odc file.

   6. Click OK, and then click Close.

3. Click any cell in the PivotTable report for which you want to unshare the data cache.

4. On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source.

   The Change PivotTable Data source dialog box appears.

5. To use a different data connection, select Use an external data source, and then click Choose Connection.

   The Existing Connections dialog box appears.

6. Select a data connection from the Select a Connection list in either the Connection files on the network or Connection files on this computer category, and then click Open.

   Note: You may need to click Browse to locate the connection file.

7. Click OK.

The PivotTable report now has a different data cache.

Top of Page