Microsoft KB Archive/133708

= Microsoft Knowledge Base =

XL: Hidden Page Fields Still Counted in PivotTable
Last reviewed: September 13, 1996

Article ID: Q133708

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows 95, version 7.0

SUMMARY
When you create a PivotTable and then hide information in the data field, the hidden information is not evaluated. However, if you hide information in the page field, the hidden fields WILL be evaluated.

WORKAROUND
To work around this situation, use either of the following methods:

Method 1: Delete the entry in the page field from the PivotTable.

Method 2: Remove the hidden fields from the source data.

MORE INFORMATION
When you add a new field label to the page field, the All item is selected by default, and the data is evaluated for all available items, even if these items are hidden.

Note that this behavior is documented in the note on page 503 of the "Users Guide," version 5.0.

Example
To create an example that demonstrates this behavior, use the following steps:

  In a new worksheet, type the following information: A1: State      B1: Men         C1: Women A2: TX         B2: 1           C2: 1 A3: CA         B3: 2           C3: 2 A4: MO         B4: 3           C4: 3 A5: MI         B5: 4           C5: 4  Select the range A1:C5, and click PivotTable on the Data menu. Click Next, and click Next again. Drag the Women field label to the page field, drag the State field label to the column field, and drag the Men field label to the data field. Click Finish. Double-click the state field.  In the Hide Items section of the PivotTable Field dialog box, hide CA, and click OK. The Grand Total box in the PivotTable sums everything except CA.   To create an additional page field, click the State field label and drag it next to Women The grand totals now show every entry, and CA is visible (even though it is selected to be hidden). 