XL: Grouping a Field in a PivotTable Changes Other PivotTable

ID: Q178767

The information in this article applies to:

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you group or ungroup a field in a PivotTable, the same field may also be grouped or ungrouped in another PivotTable.

CAUSE

This problem will occur 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.

WORKAROUND

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

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

2. In the PivotTable Wizard - Step 1 of 4 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 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.

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

Note also that 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 alert message similar to the following: 

   Your new PivotTable will use less memory if you base it on your
   existing PivotTable [<WorkbookName>]<SheetName>!<PivotTableName>,
   which was created from the same source data. Do you want your new
   PivotTable to be based on the same data as your existing PivotTable?
If you click Yes, the new PivotTable will use the old PivotTable as the source of its data, and the problem described above will occur. If you click No, the new PivotTable will use its own PivotTable memory cache, and the problem described above will not occur.

STATUS

This behavior is by design of the versions of Microsoft Excel listed at the beginning of this article.

MORE INFORMATION

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: Don't select the grey "button" for the field.
2. On the Data menu, point to Group And Outline, and click Group.

3. If necessary, change the values in the Starting At and/or Ending At 

   edit boxes. In the By list box, select the interval(s) that you want
   to group.
4. 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 clicking Ungroup.

The following steps demonstrate the problem described above:

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

   following data:

      A1: Start    B1: Sales
      A2: 1/1/98   B2: 4
      A3: 2/1/98   B3: 3
      A4: 3/1/98   B3: 2
      A4: 4/1/98   B4: 1
2. Select cell A1. On the Data menu, click PivotTable or PivotTable 
   Report.
3. In the PivotTable Wizard dialog box, follow these steps: 

    a. In Step 1, click Next.

    b. In Step 2, click Next.

    c. In Step 3, drag the Start field to the ROW area, and drag the Sales
       field to the DATA area. Then, click Next.

    d. In Step 4, enter "Sheet2!A1" (without the quotation marks) in the
       PivotTable Starting Cell edit box. Or, click the Existing Worksheet
       option button, and enter "Sheet2!A1" (without the quotation marks,
       in the edit box.

    e. Click Finish.

   A new PivotTable appears in Sheet2.
4. Activate Sheet3, and select cell A1.

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

6. In the PivotTable Wizard dialog box, follow these steps: 

    a. In Step 1, select the Another PivotTable option button. Click Next.

    b. In Step 2, select the PivotTable you created in step 3. Click Next.

    c. In Step 3, drag the Start field to the ROW area, and drag the Sales
       field to the DATA area. Then, click Next.

    d. In Step 4, click Finish.

   Another new PivotTable appears in Sheet3.
7. Select cell A3 in Sheet3. On the Data menu, point to Group And 
   Outline, and click Group.
8. In the By list, select Months. Then, click OK. 

   Note that the PivotTable in Sheet3 is now grouped by month (Jan, Feb,
   Mar, and so on).
9. Activate 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.

Additional query words: XL5 XL7 XL97 

Keywords          : xlpivot 
Version           : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999