Microsoft Knowledge Base

XL: Pivot Table Show and Hide Attributes Not Reset
Last reviewed: September 12, 1996
Article ID: Q111289
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

In Microsoft Excel version 5.0, when you remove a hidden field from a pivot table, the show and hide attributes will not be reset if you attempt to add the hidden field back into the view.

This feature is by design.

MORE INFORMATION

Once a pivot table is created and an item is unhidden to show detail, and then hidden again to hide detail, and that item is then dragged out of the view, the show and hide attribute is stored with that field item.

Adding a field back to the Pivot Table, the show and hide attributes will be the same as they were at the time the field was removed. After adding the field back to the Pivot Table, in order to show detail, the item will need to be unhidden to view details.

Steps to Reproduce Behavior

  1. In a new worksheet, type the following: 

          A1: Item     B1: Region   C1:  Year     D1: Sales
      A2: Gadget   B2: North    C2:  1991     D2: 112.64
      A3: Widget   B3: South    C3:  1991     D3:  35.38
      A4: Widget   B4: North    C4:  1992     D4:  82.22
      A5: Gadget   B5: South    C5:  1992     D5: 103.12
      A6: Widget   B6: North    C6:  1991     D6:  53.87

    and then select cell B2.

  2. From the Data menu, choose PivotTable.

  3. In Step 1 Of 4 in the PivotTable Wizard dialog box, select the Microsoft Excel List or Database option. Choose Next.

  4. In Step 2, select the range $A$1:$D$6 and choose Next.

  5. In Step 3, move the Region field to the Row area, move the Year field to the Column area, and move the Sales field to the Data area. Choose Next.

  6. In Step 4 of 4, select cell F4 to place the pivot table on the sheet, and then choose Finish.

  7. Double-click cell F6 and select Item in the Show Detail dialog box and choose OK.

  8. Double-click cell F6 again to Hide Detail.

  9. From the Data menu, choose PivotTable, drag Item out of the view, and choose Finish.

  10. Select PivotTable from the Data menu again and drag Item back into the Row area. Select Finish.

Note that the field is displayed in the table but all detail is hidden. The show and hide attribute is not reset when the field is removed from the Pivot Table. You must double-click the field to show the detail again.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.00 7.00



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.