Search Contextures Sites

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

Expand and Collapse Pivot Fields

Instead of viewing the details for all items in a pivot field, you can collapse and expand specific items, or the entire pivot field. The Expand and Collapse commands do not affect items that have been filtered.

Video: Expand and Collapse Pivot Fields

To see the steps for expanding and collapsing pivot table details, please watch this short video.

Display the Expand/Collapse Buttons

You can show or hide the the expand and collapse buttons in a pivot table. These are the small + / - signs at the left side of a pivot item heading.

collapse specific item

To show or hide the the collapse and expand buttons:

  1. Select a cell in the pivot table.
  2. On the Ribbon, under PivotTable Tools tab, click the Analyze tab
  3. Click the +/- Buttons command, to toggle the buttons on or off

    show expand collapse buttons

Expand or Collapse a Specific Pivot Item

You can expand or collapse a specific item in a pivot field, and see only its heading. The other items in that field will not be affected.

To expand or collapse a specific item:

  1. Click the Expand/Collapse button at the left of the pivot item heading
    OR, double-click on the cell with the heading.

    collapse specific item

Note: Collapse and Expand affect all occurrences of a pivot item. In the screen shot below, there are multiple instances of the Bars category, and they all are collapsed. You can't change collapse Bars in one Region only.

collapse all instances of item

Expand or Collapse the Pivot Field

Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the entire pivot field.

  1. Right-click the pivot item, then click Expand/Collapse
    In this example, I right-clicked on Boston, which is an item in the City field.
  2. Select on of the Expand/Collapse options:
    • To see the details for all items in the selected pivot field, click Expand Entire Field.
      • In this example, that will expand all the City items
    • To hide the details for the selected pivot field, click Collapse Entire Field.
      • In this example, that will collapse all the City items

expand entire field

Expand or Collapse to a Specific Level

Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the details to a specific level.

  1. Right-click the pivot item, then click Expand/Collapse
    In this example, I right-clicked on Boston, which is an item in the City field.
  2. Select on of the Expand/Collapse options:
    • To show details below the selected level, click Expand To [Field Name].
      • In this example, clicking on Expand to "Category" will expand Boston, and other expanded City items, to the Category level. Cities that are currently collapsed will not be expanded.
    • To hide details below the selected level, click Collapse To [Field Name].
      • In this example, clicking on Collapse to "Region" will collapse the East region to the Region level. Other regions will not be collapsed.

collapse to specific level

Expand or Collapse Entire Pivot Table

Unfortunately, there is no command that will expand or collapse the entire pivot table. You can use two steps to expand everthing in the Row Labels area, and the same two steps for the Column Labels area.

In the Row Labels area:

  1. Right-click on one of the headings in the outermost field, click Expand/Collapse, then click click Expand Entire Field or Collapse Entire Field.
  2. Right-click on one of the headings in the outermost field, click Expand/Collapse, then click click Expand To or Collapse To, for the lowest level field.

Repeat these two steps for the Column Labels area, if necessary.

Download the Sample File

Click here to download the Expand and Collapse sample file. The file is zipped, and in xlsx format, with no macros.

More Pivot Table Resources

Tutorials:

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 6, 2014