39

Expand and Collapse Entire Pivot Table Fields – VBA Macro

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

Expand Collapse Entire Pivot Field Macro - Rows Area

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.

Expand Collapse Fields Manually

There are shortcuts for this on the right-click menu and the Options/Analyze tab in the ribbon.

Expand Collapse Entire Field - Right-click Menu

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).

Expand Collapse Buttons on Analyze Options Tab

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.

Expand Collapse Fields 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.

Pivot Chart Buttons to Expand Collapse Rows Area

The steps to modify the pivot chart are:

  1. Delete the Values and Axis field buttons on the chart.
  2. Delete the axis labels, title, and legend.
  3. 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.

Expand Collapse Entire Fields Macro on Rows and Columns

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

Macros Assigned to Shapes to Expand Collapse Entire Pivot Field

We can assign these macros to shapes or buttons on the sheet.

Assign Macros to Shapes in Excel

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.

Expand Collapse Macro Buttons Added to Pivot Chart

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 39 comments
Robert - June 8, 2018

I love these macros! The only problem I am having is when I use them on a pivot chart in another workbook, your example file opens after the change is made in my pivot table.

Also, a suggestion to improve would be to have some sort of toggle feature for the Expand/Collapse Rows and Columns macros. Something like your Case Text macro. Then you could consolidate 4 separate macros into 2.

Reply
    Robert - June 8, 2018

    Solved my own issue, the command buttons are there for a reason!

    Reply
Matthias - May 22, 2018

Chuck had asked about “Type mismatch” for date type fields.

No need to check in 2010 as it happens also in 2013 (and most likely also in 2016).

‘Loop each pivot item ‘error happens at first pi of any date pf
For Each pi In pf.PivotItems ‘pi =”1/31/1979″ instead of 31.01.1979
‘If pivot item is collapsed then
If pi.ShowDetail = False Then ‘pi.ShowDetail =

I do not know if this matters, but my date example from above is a text from a download tranfered into date format:
Range(“AN24”).Select
With Range(Selection, Selection.End(xlDown))
.NumberFormat = “dd.mm.yyyy”
.Value = .Value
End With

Reply
Matthias - May 17, 2018

Jon, a great macro, but it seems not to work for powerpivots.

Replacing ShowDetail with DrilledDown alone is not sufficient.

The problem is that VBA can’t read the DrilledDown status, if it is not an OLAP data source (whatever that means).

This clumsy Workaround worked for me:
If WorksheetFunction.CountA(Range(Cells(19, iPosition + 1), Cells(Rows.Count, iPosition + 1))) = 0 Then

But it it is neither elegant nor flexible as the starting position below the header needs to be defined for each pivottable individually.

Do you have a better solution? Could pt.pf.DataRange be somehow applied?

Reply
Chuck - February 16, 2018

Hi Jon,

Happy Friday to all the members of your blog! By the way have you had the chance to check in Excel 2010 why would the ShowDetail by a type of Integer when it is supposed to be Boolean?

Cheers!

Reply
Jian - February 15, 2018

Thanks Jon, for this great email and great instruction!

Reply
chuck - February 12, 2018

Jon,

I finally made to work the way it is supposed to work. Here below is the Macro, I have used the on error handling and have integrated a Public variable to make the iPosition move by one.
[CODE]
Option Explicit

Public iP As Integer
Public fPath As Boolean

Sub Expand_Entire_RowField()
‘Expand the lowest position field in the Rows area
‘that is currently expanded (showing details)
If fPath = False Then
iP = 1
fPath = True
Else
iP = iP + 1
End If
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long

On Error GoTo ErrHandling
‘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 = iP 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

ErrHandling:
PI.ShowDetail = True
Resume Next

‘ Set pt = ActiveSheet.PivotTables(1)

‘ iFieldCount = pt.RowFields.Count – 1

‘ For iPosition = 1 To iFieldCount
‘ For Each pf In pt.RowFields
‘ If pf.Position = iPosition Then
‘ For Each pi In pf.PivotItems
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
‘ Exit Sub
‘ End If
‘ Next pi
‘ End If
‘ Next pf
‘ Next iPosition

End Sub
[/CODE]

Reply
Corinne - February 12, 2018

This is ace, thanks!

Reply
Olga - February 11, 2018

Hi Jon,
Great macros! Will they work for Power Pivot either?
Thanks.

Reply
Edil - February 9, 2018

Thanks Jon, for this great email.
I have not yet tested out the macros, but I wiil do.
If any query arise I’ll leave a msg. Thx again.

Reply
FrankT - February 9, 2018

Only after changing the pivot table I’ll get the same error as Chuck.

pi.ShowDetails errors when pi.Name equals “<05/01/2015"

Are there any requirements?

Reply
    Jon Acampora - February 10, 2018

    Hey Frank,
    It sounds like this might be an issues with Excel 2010. I’ll have to get a machine with 2010 on it to test. In the mean time, if anyone has a workaround/solution, please feel free to post.

    I’m not sure if the error is only raised on that pivot item, or the ShowDetails property.

    Reply
Scully - February 8, 2018

Jon, I recently started subscribing to your tips because of a new gig I have. In. Nutshell I need get better at Vlookup, Pivoting table and analyzing data between various spreadsheets at a rapid pace, like NOW.

What are your suggestions, please?

Also your tips are always AWESOME

Reply
    Jon Acampora - February 10, 2018

    Hi Scully,

    Congrats on the new gig! 🙂

    Here are some references to help. I have a free 3-part video series on the lookup formulas including VLOOKUP, INDEX, MATCH and more. Here is the link to register. It’s free.

    http://www.excelcampus.com/lookup-formulas-free-training/

    I also have a free webinar on getting started with pivot tables. Here is the link to register.

    https://www.excelcampus.com/pivot-secrets-webinar

    I hope that helps get you started. Both of those resources are precursors to my full in-depth courses.

    Thanks again for your support! Happy to have you here! 🙂

    Reply
ABRAHAM LEE - February 8, 2018

Dear Jon
Thank you so much for your email regarding Excel …. and I still want to keep in contact with you..

Again, thank you Jon

Regards,
Abraham lee

Reply
Chuck - February 8, 2018

Jon,

Sorry I think I uploaded the wrong macros. Here below is the adjustment I made to the macro not to get any more the Type mismatch error 13. This will expand all or collapse all rows or columns:
Option Explicit

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
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Months”).ShowDetail = True
‘ 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

‘ Set pt = ActiveSheet.PivotTables(1)

‘ iFieldCount = pt.RowFields.Count – 1

‘ For iPosition = 1 To iFieldCount
‘ For Each pf In pt.RowFields
‘ If pf.Position = iPosition Then
‘ For Each pi In pf.PivotItems
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
‘ Exit Sub
‘ End If
‘ Next pi
‘ End If
‘ Next pf
‘ Next iPosition

End Sub

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
ActiveSheet.PivotTables(1).PivotFields(“Months”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = False
‘ 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

Sub Expand_Entire_ColumnField()
‘Expand the lowest position field in the Columns 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

Set pt = ActiveSheet.PivotTables(1)

iFieldCount = pt.ColumnFields.Count – 1

For iPosition = 1 To iFieldCount
For Each pf In pt.ColumnFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = True
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition

End Sub

Sub Collapse_Entire_ColumnField()
‘Collapse the lowest position field in the Columns 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

‘Start at bottom
‘Loop to find if any datarange cells show details
‘if no, go to next field,
‘if yes, collapse entire field

Set pt = ActiveSheet.PivotTables(1)

iFieldCount = pt.ColumnFields.Count – 1

For iPosition = iFieldCount To 1 Step -1
For Each pf In pt.ColumnFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Quarters”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Years”).ShowDetail = False
‘ If pi.ShowDetail = True Then
‘ pf.ShowDetail = False
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition

End Sub

Option Explicit

Sub Expand_Entire_RowField_Other_Sheet()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long

Set pt = Worksheets(“Pivot Table”).PivotTables(“PivotTable1”)

iFieldCount = pt.RowFields.Count – 1

For iPosition = 1 To iFieldCount
For Each pf In pt.RowFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Customer”).ShowDetail = True
ActiveSheet.PivotTables(1).PivotFields(“Category”).ShowDetail = True
‘ If pi.ShowDetail = False Then
‘ pf.ShowDetail = True
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition

End Sub

Sub Collapse_Entire_RowField_Other_Sheet()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long

Set pt = Worksheets(“Pivot Table”).PivotTables(“PivotTable1”)

iFieldCount = pt.RowFields.Count – 1

For iPosition = iFieldCount To 1 Step -1
For Each pf In pt.RowFields
If pf.Position = iPosition Then
For Each pi In pf.PivotItems
ActiveSheet.PivotTables(1).PivotFields(“Category”).ShowDetail = False
ActiveSheet.PivotTables(1).PivotFields(“Customer”).ShowDetail = False
‘ If pi.ShowDetail = True Then
‘ pf.ShowDetail = False
Exit Sub
‘ End If
Next pi
End If
Next pf
Next iPosition

End Sub

Reply
    Jon Acampora - February 10, 2018

    Hey Chuck,
    Thanks for sharing. I don’t have a machine with 2010 on it right now to test. We might also be able to add some error handling around the ShowDetails check to bypass those errors. I’m not exactly sure what is causing it though.

    The one important thing to note for others using your macro is that you will need to change the field names to match whatever fields are used in the pivot table.

    Thanks again!

    Reply
      Chuck - February 13, 2018

      Jon,

      While investigating using the debugger I opened the watch in the Immediate Window and found that pi.ShowDetail had a type of Integer when it is supposed to be a Boolean. I could not figure out why the type is set as Integer? I wished I could integrate a snapshot of the debugger with the value within this message.

      Regards,

      Reply
George - February 8, 2018

Jon:
How is the date hierarchy automatically detected without a supporting date dimension to provide the added date intelligence for the roll-up & drill-down?
GW

Reply
    Jon Acampora - February 10, 2018

    Hi George,
    Great question! The hierarchy is created with the Group feature of the pivot table. Here is an article that explains the Date Grouping in a Pivot Table versus using formulas. I hope that helps. Thanks! 🙂

    Reply
      GW - February 11, 2018

      Jon:
      Thanks. Proves that we all

      Reply
      GW - February 11, 2018

      Jon:
      Thanks. Proves that we all have lots to learn. I am looking forward to continuing the lifelong quest for continuous learning!
      GW

      Reply
      George - February 11, 2018

      Jon:
      Thanks. Proves that we all have lots to learn. I am looking forward to continuing the lifelong quest for continuous learning!
      George

      Reply
Nigel Forge - February 8, 2018

Excellent macro Jon, both functional and educational. I am enjoying your course so much, and learning every time I take another lesson module.
I am looking forward to using your training to get some of my spreadsheets functioning in a way more efficient manner!

Reply
    Jon Acampora - February 10, 2018

    Thank you so much Nigel! I’m excited to have you as a member of The VBA Pro Course, and happy to hear you are learning more about VBA. 🙂

    Reply
Chuck - February 8, 2018

Hi Jon,

For some reason the macro results in an error type: “Type Mismatch” at the following command line:

” If pi.ShowDetail = False Then”
and when verifying the content of the variable pi in
” For Each pi In pf.PivotItems
the value contained in pi is “<05/01/2015" could it be because my date setting on my desktop being "dd-mm-yyyy"?

Just wondering. I am actually using Excel 2010.

Reply
    FrankT - February 8, 2018

    I’ll get the same error but only after changing the pivot table configuration.

    Under certain conditions pi.ShowDetail errors when pi.name equals “<05/01/2015".

    @Jon,
    are there any requirements?

    Reply
    FrankT - February 9, 2018

    Only after changes to the pivot table I’ll get the same error.

    pi.ShowDetail errors when pi.Name equals “<05/01/2015"

    @Jon,
    are there any requirements?

    Reply
Irfan - February 8, 2018

A lot of thanks Jon

Reply
Kevin Lehrbass - February 8, 2018

Hi Jon,

I didn’t realize Pivot Charts in Excel 2016 had those buttons. Thanks for creating this! Did someone ask you for this or did you think it would be fun to create?

Cheers,
Kevin
http://www.myspreadsheetlab.com/blog/

Reply
    Jon Acampora - February 10, 2018

    Thanks Kevin! 🙂 This macro was inspired by a different question about groups, which I’ll cover in a future post.

    We can also use those pivot chart buttons in place of the macro buttons if your groups are in the Rows area. I added a screencast animation in the section on PivotCharts above to show what that looks like.

    Reply
Thanasis Adraktas - February 8, 2018

Great job…I really apreciate it.

Reply
Volodymyr - February 8, 2018

Hi Jon. I like your idea with the macro very much but it doesn’t seem to work for me.
Is it only for Office 2016?
I’m using 2010.

Reply
    Jon Acampora - February 10, 2018

    Hi Volodymyr,
    I believe it should work in 2010, but haven’t tested. Are you getting an error with the example file you can download above?

    Reply
hocine satour - February 7, 2018

Great!
Thank you Jon

Reply
    Jon Acampora - February 10, 2018

    Thanks for your support Hocine! 🙂

    Reply

Leave a Reply: