Excel Macro Keeps One Pivot Item Collapsed

Someone asked me if it’s possible to prevent one item in a pivot table from being expanded, to show its details. They wanted all the other items to be expandable though. There’s nothing built in that will allow this, so I had to use programming instead. This macro keeps one pivot item collapsed in a pivot table, but doesn’t affect any other pivot items. Modify the code, to use the pivot field and pivot item names in your pivot table.

Expand and Collapse Pivot Fields

Before we look at the macro, you might want to watch this short video. It shows a few different ways that you can manually expand and collapse the pivot items in an Excel pivot table.

Why Keep One Pivot Item Collapsed?

The person who asked for this “keep one pivot item collapsed” feature didn’t say why it was needed.

So here are a few possible reasons that you’d want to do this:

The category has hundreds of items, and there’s never any need to show all of them
The pivot table will be printed in a report, and you don’t want readers to see details for that category
There’s private information in that category, which you don’t want people using the pivot table to see

In this example, the macro will keep the Crackers item collapsed, in the Category field.

Pivot Table Privacy

The macro shown below will make sure that the details for the Crackers field will stay hidden in the pivot table. That will help with the first 2 reasons in the list above.

However, hiding things in the pivot table won’t ensure that the hidden information remains private. There are many ways to work around pivot table protection and pivot table restrictions. So, keep that in mind, if you use this macro.

For example, if you double-click on the cell that shows the total Quantity for Crackers, the underlying records will be sent to a new sheet in the workbook, showing all the “hidden” data.

Macro to Keep One Pivot Item Collapsed

In this code sample, the field name is “Category” and the “Crackers” pivot item cannot be expanded, because the code prevents that from happening. Clicking on the Expand button does not show the products in that category.

First, add this code to the pivot table’s worksheet code module in your workbook.
Then, modify the variable settings, to use the pivot table, pivot field and pivot item names in your workbook.

This code will run automatically the the pivot table is changed (PivotTableUpdate event), and keep one pivot item collapsed.

_____________

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
Dim pt As PivotTable
Dim myPT As String
Dim myPF As String
Dim myPI As String

myPT = "ptSalesCat"
myPF = "Category"
myPI = "Crackers"
Set pt = ActiveSheet.PivotTables(myPT)

If Target.Name = pt.Name Then
  Application.EnableEvents = False
  With pt.PivotFields(myPF) _
    .PivotItems(myPI)
    If .ShowDetail = True Then
      Application.ScreenUpdating = False
      .ShowDetail = False
    End If
  End With
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

__________

Get the Sample File

To get the sample file with the macro to keep one pivot item collapsed, go to the Expand and Collapse page on my Contextures website.

In the download section, click the link to get the file with the Keep Collapsed macro. The Excel file is zipped, and in xlsm format, and contains one macro, on the sheet with the pivot table.

_________________________