Knowledge Base

XL97: Hidden Page Fields Items No Longer Counted in PivotTable Summary

Article ID: 203268

Article Last Modified on 10/22/2000

APPLIES TO

This article was previously published under Q203268

SYMPTOMS

When you hide items in a page field in a Microsoft Excel PivotTable, the data associated with these hidden items is not counted in the summary (subtotals and grand totals). However, when you open workbooks created in earlier versions of Excel that contain a PivotTable with hidden page field items, the data associated with these hidden items is counted in the subtotals and grand totals.

CAUSE

This behavior changed in Microsoft Excel 97. Excel 97 automatically excludes hidden page field items in subtotals and grand totals.

To see an example of this behavior, see the "More Information" section later in this article.

RESOLUTION

The Subtotal hidden page items check box in Excel 97 lets you include or exclude hidden page field items in the subtotals. To modify this check box, follow these steps:
  1. Click a cell inside the PivotTable range.
  2. Click PivotTable on the PivotTable toolbar and then click Options.
  3. Click to select or clear the Subtotal hidden page items check box.

MORE INFORMATION

Example

  1. Type the following data in a new worksheet in Microsoft Excel 97:
     A1: Name   B1: Month   C1: Sales
 A2: Bob    B2: Jan     C2: 1
 A3: Bob    B3: Feb     B3: 2
 A4: Bob    B4: Mar     C4: 3
 A5: Fred   B5: Jan     C5: 1
 A6: Fred   B6: Feb     C6: 2
 A7: Fred   B7: Mar     C7: 3
 A8: Jane   B8: Jan     C8: 1
 A9: Jane   B9: Feb     C9: 2
A10: Jane  B10: Mar    C10: 3
  2. Click PivotTable Report on the Data menu.
  3. In the PivotTable Wizard - Step 1 of 4 dialog box, click Next.
  4. In the PivotTable Wizard - Step 2 of 4 dialog box, in the Range box, type $A$1:$C$10, and click Next.
  5. In the PivotTable Wizard - Step 3 of 4 dialog box, drag the Name field to the Page area. Drag the Month field to the Row area on the PivotTable. Drag the Sales field to the Data area on the PivotTable, and then click Next.
  6. In the PivotTable Wizard - Step 4 of 4 dialog box, click Existing Worksheet. Type =$E$1 and click Finish.

    Note that when Excel generates the PivotTable, the grand total is 18.
  7. Double-click the Name field in the table and click to hide the item Fred. Click OK.
When you go back to the table, you cannot select Fred from the list of available fields. The total changes to 12. If you follow these steps in earlier versions of Excel, the total is 18. Also, if you open the workbook in Excel 97, the total continues to be 18.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

133333 XL: Hidden Page Fields Counted in Summary

Keywords: kbinfo KB203268


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.