Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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.

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


Here is our Pivot Table.

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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!

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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.

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

All of the Products are now expanded!

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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

Expand and Collapse buttons

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

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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.

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

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

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline

 

Click here to learn all about Excel Pivot Tables!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Linkedin