XL: PivotTable Source Changes From Another PivotTable

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, if you run the PivotTable Wizard on a PivotTable that you created from data in another PivotTable, the option selected in the Create PivotTable From Data In list is the data source of the original PivotTable, rather than the Another PivotTable option you selected to create the PivotTable.

CAUSE

This behavior is by design so that you can see or change your original data source for the PivotTable that was created from data in the existing PivotTable. The two pivot tables still use the same data cache, unless you edit the reference (when your table is based on data from a Microsoft Excel list, database, or from multiple worksheet ranges) or you edit the query for one of the PivotTables (when your data is from an external data source).

WORKAROUND

If you edit the range or query for one of the PivotTables, another cache is created so that each PivotTable has its own cache. To edit one of the PivotTables so that they again share the same cache, do the following:

1. Click in the PivotTable that you want to update to have the same data cache as another PivotTable in the same workbook.

2. On the Data menu, click PivotTable or PivotTable Report.

3. In the PivotTable Wizard dialog box, click Back until you are in the PivotTable Wizard - Step 1 of 4 dialog box.

4. In the PivotTable Wizard - Step 1 of 4 dialog box, under Create PivotTable From Data In list, select Another PivotTable. Click Next.

5. From the "Select The PivotTable That Contains The Data You Want To Use" list, select the desired PivotTable. Click Finish.

MORE INFORMATION

When you create a PivotTable, you can use the Another PivotTable option in the PivotTable Wizard as the data source if the current workbook already contains a PivotTable. When you use this option, the same cache is used for the other PivotTable or Tables, limiting the number of copies of the source data in memory. However, if you then edit the original data source for a PivotTable that is sharing a data cache with another PivotTable, a new cache is created for the PivotTable you edit.

NOTE: When you create a PivotTable from data in an External Data Source, the data cache consists of the query string and the data returned from the query.

REFERENCES

For an Overview Of Creating A PivotTable, click the Search button in Help and type:

   PivotTable Wizard