Expand or Collapse Multiple Excel PivotTable Fields with VBA
Question details
Automatically expand or collapse multiple PivotTable fields using correctly declared VBA variables.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants a macro to change the expansion state of multiple PivotTable fields.
- Observed behavior
- The macro needs to reference the declared PivotField variables rather than an incorrect or undeclared variable.
Identify the worksheet, PivotTable, and field names in your workbook. Choose row fields that have lower-level details to expand or collapse, and make sure macros are enabled if you plan to run VBA.
Use declared field variables in a VBA macro
Use this approach to automate expansion or collapse across multiple PivotTable fields.
The example assigns each PivotField to a declared variable, then changes the ShowDetail property of its visible PivotItems. Replace the worksheet, PivotTable, and field names with those in your workbook.
ShowDetail requires items with expandable or collapsible detail. Behavior and available operations can vary with the PivotTable layout and data source.
In Excel, select Developer > Visual Basic, then choose Insert > Module.
Paste this code into the module: Option Explicit Sub SetPivotDetails() Dim pt As PivotTable Dim pfRegion As PivotField Dim pfProduct As PivotField Dim expandItems As Boolean Set pt = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1") Set pfRegion = pt.PivotFields("Region") Set pfProduct = pt.PivotFields("Product") expandItems = True SetFieldDetails pfRegion, expandItems SetFieldDetails pfProduct, expandItems End Sub Private Sub SetFieldDetails(ByVal pf As PivotField, ByVal expandItems As Boolean) Dim item As PivotItem For Each item In pf.PivotItems If item.Visible Then item.ShowDetail = expandItems Next item End Sub
Edit Sheet1, PivotTable1, Region, and Product to match your workbook. Set expandItems to True to expand or False to collapse, then run SetPivotDetails from Developer > Macros.

Expand or collapse PivotTable items from the menu
Use the built-in menu when you want to change a field without running a macro.
Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Its Excel format compatibility can make migration easier, though it does not fix Excel-specific VBA issues.

Frequently Asked Questions
Why does my PivotTable macro refer to an undeclared variable?
Check the variable names in the VBA editor and use the PivotField variables declared with Dim and assigned with Set. Adding Option Explicit at the top of the module helps catch undeclared names.
How do I switch the macro from expanding to collapsing?
In SetPivotDetails, change expandItems from True to False, then run the macro again from Developer > Macros.
Why can ShowDetail produce an error?
An item may not have lower-level detail, or the PivotTable data source may restrict the operation. Check the field hierarchy and whether the item can be expanded or collapsed manually.
Can I collapse a whole field without VBA?
Yes. Right-click an item in the field, select Expand/Collapse, and choose Collapse Entire Field when that command is available.




