XL: Grouping a Field in a PivotTable Changes Other PivotTable

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

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?

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.

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.