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

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.

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

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!

Thanks Jon, for this great email and great instruction!

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]

This is ace, thanks!

Hi Jon,

Great macros! Will they work for Power Pivot either?

Thanks.

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.

Thanks Edil! 🙂

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

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

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

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

Jon:

Thanks. Proves that we all

Jon:

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

GW

Jon:

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

George

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!

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

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?

A lot of thanks Jon

Thanks Irfan! 🙂

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/

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.

Great job…I really apreciate it.

Thanks Thanasis! 🙂

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.

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?

Great!

Thank you Jon

Thanks for your support Hocine! 🙂