Excel Pivot Table Shortcut to Expand and Collapse

After you set up a pivot table, you can use the plus and minus buttons to show or hide the pivot table details. For example, hide details for the East region, so only its totals are showing, and leave all the West region details visible. Do you know the pivot table shortcut to expand and collapse the details? Keep reading, to see a quick and easy way to do this.

Expand and Collapse Buttons

By default, there are little plus and minus signs in a pivot table, to the left of the pivot item labels. These pivot table Expand and Collapse buttons let you show or hide the details for a specific item, such as the Bars category (shown below), or an entire field, like Category.

NOTE: When you click a minus button, to collapse an item, all instances of that pivot item are collapsed. In the screen shot below, the Bars category is collapsed for both the East and the North regions.

Hide the Expand and Collapse Buttons

Unless you change the default pivot table settings, the expand and collapse buttons appear automatically when you create a new pivot table.

If you want to hide the expand and collapse buttons, follow these steps:

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

Shortcut to Expand and Collapse

Did you know that you can use the mouse scroll wheel to expand and collapse the field details in a pivot table?

In a pivot table, point to a cell in the Row or Column area.

• To expand, press Shift and scroll UP with the mouse wheel
• To collapse, press Shift and scroll DOWN with the mouse wheel

TIP: When collapsing, point to a cell near the top of the pivot table, so the pointer doesn’t end up outside of the pivot table range.

You’ll get different results, based on the cell you’re pointing at, so experiment to see how it works. There are a few examples shown in the next section.

Outer and Inner Fields

There are 2 types of fields in the Row area:

• Outer fields – have one or more fields below them in the PivotTable Field List
• Inner fields – have no fields below them in the PivotTable Field List

In this pivot table, Region, City and Category are outer fields, and Product is the inner field.

Shortcut Examples

Here are a couple of examples of what happens when you use the Shift and Scroll shortcut.

First Outer Field

Region is the first outer field, at the far left of the Row area.

• Point to a cell in the Region field
• Press Shift and scroll the mouse wheel DOWN, to collapse the pivot table details down to the Region field

To expand the pivot table again, point to a cell in the Region field, press Shift, and scroll the mouse wheel UP

Third Outer Field

Category is the third outer field, below Region and City the Row area.

• Point to a cell in the Category field
• Press Shift and scroll the mouse wheel DOWN, once
• The pivot table details are hidden for the Category and  Product fields

To expand the pivot table again, point to a cell in the Category field, press Shift, and scroll the mouse wheel UP

Inner Field

Product is the Inner field, at the far right of the Row area.

• Point to a cell in the Product field
• Press Shift and scroll the mouse wheel DOWN, once

Because Product is the inner field, there are no details below it to collapse.

• Instead, the pivot table collapses to the last outer field – Category
• If you continue to press Shift and scroll DOWN, the remaining fields will collapse, one by one

Expand on the Inner Field

If you point to a cell in the Inner field, Product, press Shift and scroll UP to expand, you’ll see the Show Details dialog box.

If you select one of the fields, it is added to the Row area, as the new Inner field. In the screen shot below, Store was added to the pivot table.

NOTE: If you add a field accidentally, click the Undo button, or press Ctrl+Z, to remove it.

More Expand and Collapse Tips

There are more Pivot Table Collapse/Expand tips on my Contextures website. You’ll also find sample files and pivot table macro examples that help you show or hide the pivot table details.

And for a quick overview of hiding and showing details in a pivot table, watch this short video. It shows a few different ways that you can manually expand and collapse the pivot items in an Excel pivot table.

__________________________

Pivot Table Shortcut to Expand and Collapse

________________________