Microsoft Knowledge Base

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

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

2. Select the range A1:C5, and click PivotTable on the Data menu. Click Next, and click Next again.

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

4. Double-click the state field.

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

6. 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).

REFERENCES

Microsoft Excel "User's Guide," version 5.0, page 503