XL: Grouping a Field in a PivotTable Changes Other PivotTable

ID: Q178767

The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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:

    1. In Step 1, click Next.


    2. In Step 2, click Next.


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


    4. In Step 4, enter Sheet2!A1 in the PivotTable Starting Cell edit box. Or, click the Existing Worksheet option button, and enter Sheet2!A1, in the edit box.


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

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


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


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


    4. 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: September 16, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.