Expand and Collapse buttons in Excel Pivot Tables

The magic with Pivot Tables is it is very easy to expand rows to show additional details for your analysis, then to quickly collapse and hide that additional data. It conveniently has expand and collapse buttons for easily viewing and hiding additional data. I will show you quickly how to use that for your Pivot Table rows and columns.

Here is our Pivot Table.

How to Expand and Collapse buttons in Excel Pivot Tables

STEP 1: You can quickly click on the – button to collapse the Product Field, or the + button to expand the Product Field. Give it a try!

You can see the ICE CUBES is now collapsed, while BOTTLES has expanded.

STEP 2: If you want this change to happen for all of the Products, right-click on a Product Field (Bottles) and select Expand/Collapse > Expand Entire Field

You can select the Collapse Entire Field option instead if you want to collapse all of the Product Fields.

All of the Products are now expanded!

STEP 3: What if we want to expand the Salesperson field? Right-click on any Salesperson and select Expand/Collapse > Expand Entire Field

STEP 4: You will get a selection to add that detail under the Salesperson field. Select SALES QTR and click OK

STEP 5: Now you have an additional level of detail (Sales Quarter) under Salesperson for your analysis!

You have the same functionality for columns as well, drag the SALES QTR field from Rows to Columns

STEP 6: Now our column of Sales Year now has the additional detail of Sales Quarters.

You can quickly click on the – button to collapse the Sales Year Field, or the + button to expand the Sales Year Field.

You can now see we have collapsed the 2012 column while expanding the 2013 column using expand and collapse buttons.

Frequently Asked Questions

How do I enable Expand and Collapse buttons in a Pivot Table?
They usually appear automatically, but you can enable them by right-clicking a Pivot Table field and selecting “Show Expand/Collapse Buttons.”

Can I remove the Expand and Collapse buttons from my Pivot Table?
Yes, you can hide them by selecting the Pivot Table, going to the “Analyze” tab, and unchecking “Show Expand/Collapse Buttons.”

Do Expand and Collapse buttons work for all types of Pivot Table fields?
They work for fields that are grouped or hierarchical, such as dates grouped by year and quarter or products grouped by category.

Will expanding or collapsing data change my Pivot Table values?
No, expanding or collapsing only affects how the data is displayed, not the underlying values or calculations.

Is there a shortcut to expand or collapse all groups at once in a Pivot Table?
Yes, right-click any Expand/Collapse button and select “Expand Entire Field” or “Collapse Entire Field” for quick navigation.