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.

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.

 

Last updated: July 12, 2013