Microsoft KB Archive/203268

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

Article ID: 203268

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



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.



Example
  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  Click PivotTable Report on the Data menu. In the PivotTable Wizard - Step 1 of 4 dialog box, click Next. In the PivotTable Wizard - Step 2 of 4 dialog box, in the Range box, type $A$1:$C$10, and click Next. 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.</li> 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.</li> Double-click the Name field in the table and click to hide the item Fred. Click OK.</li></ol>

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.

<div class="references_section">