Bottom line: Learn how to add buttons to your pivot tables and pivot charts to quickly expand/collapse entire fields using a VBA macro.
Skill level: Intermediate
Expanding & Collapsing Fields is Time Consuming
Expanding and collapsing entire fields in a pivot table can be a multi-step process that is a bit time consuming. This is especially true for the users/consumers of your Excel report files that are not familiar with the process.
To expand or collapse the ENTIRE field, we tend to press the little expand (+) and collapse (-) field buttons for each item in the field.
There are shortcuts for this on the right-click menu and the Options/Analyze tab in the ribbon.
We can also press the Expand Field and Collapse Field buttons on the Analyze/Options tab of the ribbon. A cell inside the Rows or Columns area of the pivot table must be selected for these buttons to work (be enabled).
Keyboard shortcuts to Expand/Collapse fields:
Expand Entire Field: Alt, A, J Or Menu Key, E, E
Collapse Entire Field: Alt, A, H Or Menu Key, E, C
Note: A cell in the Rows or Columns area must be selected for the keyboard shortcuts to work.
However, there are no dedicated buttons on the pivot table to expand/collapse the entire field.
New Expand Collapse Buttons in Excel 2016 Pivot Charts
In Excel 2016 a new feature was added to pivot charts that allows us to expand or collapse the fields in the Rows area. You will see little plus and minus button in the bottom right corner of your pivot chart.
However, this feature is ONLY available in Excel 2016 (Office 365). So, I created a few macros that will work on any version of Excel, in both the Rows and Columns area of a pivot table or pivot chart.
We can also modify the pivot chart to just display the expand/collapse buttons, and add them above the pivot table. Here is an animation that shows how to do that.
The steps to modify the pivot chart are:
- Delete the Values and Axis field buttons on the chart.
- Delete the axis labels, title, and legend.
- Resize the chart to only display the Expand/Collapse buttons.
The chart will be hidden behind the buttons. So we are just using the pivot chart for the expand/collapse buttons. It's important to note that this will only work for fields in the Rows area of the pivot table, and only in Excel 2016 (Office 365).
Expand Collapse Entire Pivot Fields Macros
This set of macros will expand or collapse the NEXT field in the rows or columns area. This allows us to drill down to show the details of the fields in the Rows or Columns Area.
A good example is when the pivot table has fields in the rows area for Year, Quarter, Month, Day. We might want to compare year totals, then drill down to see totals by quarter or month.
If the pivot table is currently collapsed to years, the “Expand_Entire_RowField” macro will expand ALL of the Year items to display the Quarters for each year.
Running the “Collapse_Entire_RowField” macro will collapse the Year items back.
You can download the file that contains the macros below.
Expand Collapse Entire Fields Macro.xlsm (68.3 KB)
Assign the Macros to Shapes or Buttons
We can assign these macros to shapes or buttons on the sheet.
The buttons can be continually pressed to drill up or down through the fields. This makes it easy for users of your reports to quickly expand or collapse entire fields.
The shapes can be pasted inside a pivot chart to mimic the functionality of the expand/collapse buttons in Excel 2016.
The VBA Macro Code
There are four different macros for the expand/collapse on the Rows and Columns areas. The macros use For Next Loops to do some pretty aggressive looping through the pivot fields and items.
The Expand macro finds the field in the highest position that is collapsed by checking the ShowDetail property of each PivotItem. If it does NOT find an expanded item, then it expands the entire field. If all items are expanded, then it proceeds to the field in the next position down.
Sub Expand_Entire_RowField() 'Expand the lowest position field in the Rows area 'that is currently expanded (showing details) Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim iFieldCount As Long Dim iPosition As Long 'Create reference to 1st pivot table on sheet 'Can be changed to reference a specific sheet or pivot table. Set pt = ActiveSheet.PivotTables(1) 'Count fields in Rows area minus 1 (last field can't be expanded) iFieldCount = pt.RowFields.Count - 1 'Loop by position of field For iPosition = 1 To iFieldCount 'Loop fields in Rows area For Each pf In pt.RowFields 'If position matches first loop variable then If pf.Position = iPosition Then 'Loop each pivot item For Each pi In pf.PivotItems 'If pivot item is collapsed then If pi.ShowDetail = False Then 'Expand entire field pf.ShowDetail = True 'Exit the macro Exit Sub End If Next pi End If Next pf 'If the Exit Sub line is not hit then the 'loop will continue to the next field position Next iPosition End Sub
The Collapse macro does the opposite. It starts at the lowest field position and works backwards until it finds a pivot item that is NOT collapsed. If it finds an expanded item then it collapses the entire field. Otherwise it moves up to the field in the next position in the Rows area, and repeats the process.
Sub Collapse_Entire_RowField() 'Collapse the lowest position field in the Rows area 'that is currently expanded (showing details) Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim iFieldCount As Long Dim iPosition As Long 'Create reference to 1st pivot table on sheet 'Can be changed to reference a specific sheet or pivot table. Set pt = ActiveSheet.PivotTables(1) 'Count fields in Rows area minus 1 (last field can't be expanded) iFieldCount = pt.RowFields.Count - 1 'Loop backwards by position of field (step -1) For iPosition = iFieldCount To 1 Step -1 'Loop fields in Rows area For Each pf In pt.RowFields 'If position matches first loop variable then If pf.Position = iPosition Then 'Loop each pivot item For Each pi In pf.PivotItems 'If pivot item is expanded then If pi.ShowDetail = True Then 'Collapse entire field pf.ShowDetail = False 'Exit the macro Exit Sub End If Next pi End If Next pf 'If the Exit Sub line is not hit then the 'loop will continue to the next field position Next iPosition End Sub
Download the File
You can download the file that contains the macros for the Rows and Columns areas.
Expand Collapse Entire Fields Macro.xlsm (68.3 KB)
How Can We Improve It?
The Expand/Collapse Macro buttons should make a nice addition to any report file you send to users that aren't Excel ninjas. I've seen many users click the small expand/collapse buttons repeatedly for each pivot item, simply because they didn't know there was a better way. This macro should help give them a faster and easier way to drill down through your pivot tables or charts.
Please leave a comment below with questions or suggestions on how we can improve this. Thanks so much! 🙂