Pinterest Pixel

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... read more

Download Excel Workbook
Bryan
Posted on

Overview

Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline 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 workbookExpand-Collapse-buttons.xlsx


Here is our Pivot Table.

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!

Expand and Collapse buttons in Excel Pivot Tables

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

Expand and Collapse buttons in Excel Pivot Tables

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.

See also  Group Sales with a Pivot Table

Expand and Collapse buttons in Excel Pivot Tables

All of the Products are now expanded!

Expand and Collapse buttons in Excel Pivot Tables

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 in Excel Pivot Tables

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

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 Expand and Collapse buttons in Excel Pivot Tables

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

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

 

Click here to learn all about Excel Pivot Tables!

See also  Month over Month Comparison Charts in Excel Pivot Tables

 

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

 

 

If you like this Excel tip, please share it
Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline
Expand and Collapse buttons in Excel Pivot Tables | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...