Pivot Table Issues

Expand or Collapse Multiple Excel PivotTable Fields with VBA

Aamir Naveed AkramAamir Naveed Akram Nov 14, 2026 868 views

Question details

Automatically expand or collapse multiple PivotTable fields using correctly declared VBA variables.

Expand or Collapse Multiple Excel PivotTable Fields with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, select Developer > Visual Basic, then choose Insert > Module.

2
Add the macro

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

3
Set names and run

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.

Use declared field variables in a VBA macro
Check the field layout: If an item has no lower-level detail or the data source does not support the requested operation, ShowDetail may raise an error. Select appropriate fields and check the PivotTable layout.
Free Microsoft Office alternative

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.

Work with spreadsheet data and PivotTables in WPS SpreadsheetOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday spreadsheet tasksMove to a lightweight office suite with a free option
microsoft office alternative - wps office

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.