XL2000: Grouping a Field in a PivotTable Changes Other PivotTable

In Microsoft Excel 2000, if you group or ungroup a field in a PivotTable, the same field may also be grouped or ungrouped in another PivotTable.

This problem occurs if one PivotTable uses another PivotTable as the source of its data.

Specifically, this problem occurs because when you create a PivotTable that uses another PivotTable as the source of its data, both PivotTables share a common PivotTable memory cache. This cache contains not only the data used by both PivotTables, but also the settings for the PivotTables, including grouping settings. As a result, when you group or ungroup a field in one PivotTable, the same field is grouped or ungrouped in the other PivotTable.

To prevent this problem from occurring, when you create a new PivotTable, do not use another PivotTable as the source of its data. To avoid this problem, follow these steps:

1. On the Data menu, click PivotTable and PivotChart Report.
2. In the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box, select one of the following three option buttons
   • Microsoft Excel List or Database
   • External Data Source
   • Multiple Consolidation Ranges
   depending on the location of the source data.
3. Click Next, and then proceed through the remainder of the PivotTable creation process.

If you have already created the new PivotTable, you must delete the existing PivotTable, and then create another new PivotTable using the steps above.

Using this workaround will increase the size of your workbook, because each PivotTable will have its own PivotTable memory cache.

If you create a PivotTable that uses the same source data as another PivotTable, but you do not use the other PivotTable as the source of the data, you may receive an error message similar to the following: If you click Yes, the new PivotTable uses the old PivotTable as the source of its data, and the problem described in the "Symptoms" section of this article will occur. If you click No, the new PivotTable uses its own PivotTable memory cache, and the problem described in the "Symptoms" section of this article will not occur.

In Microsoft Excel, you can group numeric fields in a PivotTable by different intervals of time, for example: seconds, minutes, hours, days, months, quarters, and years.

To group a field, follow these steps:

1. Select a cell in the field in the PivotTable that you want to group.
   
   NOTE: Do not select the gray button for the field.
2. On the Data menu, point to Group and Outline, and then click Group.
3. Click OK.

The selected field in the PivotTable is grouped using the interval(s) you selected.

You can ungroup a field by selecting a cell in the field, clicking the Data menu, pointing to Group and Outline, and then clicking Ungroup.

The following steps demonstrate the problem described in the "Symptoms" section of this article:

1. In Microsoft Excel, create a new workbook. In Sheet1, enter the following data:

      A1: Start    B1: Sales
      A2: 1/1/99   B2: 4
      A3: 2/1/99   B3: 3
      A4: 3/1/99   B3: 2
      A4: 4/1/99   B4: 1
   					

2. Select cell A1. On the Data menu, click PivotTable and PivotChart Report.
3. In the PivotTable and PivotChart Wizard dialog box, follow these steps:
   
   
   1. In step 1 of the wizard, click Next.
   2. In step 2 of the wizard, click Next.
   3. In step 3 of the wizard, click Layout, drag the Start field to the ROW area, and then drag the Sales field to the DATA area. Click OK.
   4. In step 3 of the wizard, click the Existing Worksheet button, and then type Sheet2!A1 in the edit box.
   5. Click Finish.
   A new PivotTable appears in Sheet2.
4. Switch to Sheet3, and then select cell A1.
5. On the Data menu, click PivotTable and PivotChart Report.
6. In the PivotTable and PivotChart Wizard dialog box, follow these steps:
   
   
   1. In step 1 of the wizard, select the Another PivotTable or PivotChart option button, and then click Next.
   2. In step 2 of the wizard, select the PivotTable you created in step 3, and then click Next.
   3. In step 3 of the wizard, click Layout and drag the Start field to the ROW area, and then drag the Sales field to the DATA area. Click OK.
   4. Click Finish.
   Another new PivotTable appears in Sheet3.
7. Select cell A4 in Sheet3. On the Data menu, point to Group and Outline, and then click Group.
8. In the By list, select Months, and then click OK.
   
   Note that the PivotTable in Sheet3 is now grouped by month (Jan, Feb, Mar, and so on).
9. Switch to Sheet2.

The PivotTable in Sheet2 is also grouped by month, even though you did not group the field in the PivotTable. This occurs because the two PivotTables share a common PivotTable memory cache.