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

40 comments

Your email address will not be published. Required fields are marked *

  • Super excited to use your macros! Thank you for taking the time to share and post the code! 🙂 Only “improvement” I would suggest is to add a comment in the top with how you like to be memorialized. I like to give credit to my sources in my comments at top of macros (for my own reference if I need to return for more info / related question… and to give proper credit). True, I can post my own, but commenting to give yourself credit also is a gentle reminder that someone did the heavy lifting for me… 🙂 Again, THANK YOU for posting!

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

  • 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

  • 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?

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

  • 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]

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

  • 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?

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

  • 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

  • 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

  • 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

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

      • 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,

  • 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

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

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

    • 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?

    • 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?

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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar