How To Expand And Collapse Pivot Table Fields Levels - Contextures

• Topics
  • Charts
  • Data Entry
  • Data Validation
  • Filters
  • Formatting
  • Formulas
  • Macros
  • Pivot Tables
  • Sample Data
  • Sample Files
  • Tutorial Index

Expand and Collapse Pivot Fields

Home > Pivot > Layout > Expand How to collapse and expand specific items in a Microsoft Excel pivot field, or the entire pivot field. The Expand and Collapse commands do not affect items that have been filtered

Video: Expand and Collapse Pivot Fields

Display the Expand/Collapse Buttons

Expand or Collapse a Specific Pivot Item

Expand or Collapse the Pivot Field

Expand or Collapse to a Specific Level

Expand or Collapse Entire Pivot Table

Macro to Keep One Item Collapsed

Video Transcript

Get the Sample File

More Pivot Table Tutorials

Video: Expand and Collapse Pivot Fields

To see the steps for expanding and collapsing pivot table details, please watch this short video. The full video transcript is at the bottom of this page

Display Expand/Collapse Buttons

You can show or hide the the expand and collapse buttons in a pivot table. These are the small + / - signs at the left side of a pivot item heading.

To show or hide the the collapse and expand buttons:

1. Select a cell in the pivot table.
2. On the Ribbon, under PivotTable Tools tab, click the Analyze tab
3. Click the +/- Buttons command, to toggle the buttons on or off

   

Expand or Collapse Specific Pivot Item

You can expand or collapse a specific item in a pivot field, and see only its heading. The other items in that field will not be affected.

To expand or collapse a specific item:

1. Click the Expand/Collapse button at the left of the pivot item heading OR, double-click on the cell with the heading.

   

The Collapse and Expand buttons (or double-click) affect all occurrences of a pivot item.

In the screen shot below, there are multiple instances of the Bars category, and they all are collapsed.

• You CANNOT collapse Bars in one Region only.
• All occurrences of a pivot item are collapsed together

Innermost Pivot Field

Items in the innermost pivot field do not have an expand/collapse button.

If you double-click on an innermost pivot field item:

• If there are Value fields, the Show Detail dialog box will appear.
• If there are no Value fields, you'll hear a warning sound, and nothing happens in the pivot table

Expand or Collapse Pivot Field

Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the entire pivot field.

1. Right-click the pivot item, then click Expand/Collapse
• In this example, I right-clicked on Boston, which is an item in the City field.
2. Select one of the Expand/Collapse options:
• To see the details for all items in the selected pivot field, click Expand Entire Field.
  • In this example, that will expand all the City items
• To hide the details for the selected pivot field, click Collapse Entire Field.
  • In this example, that will collapse all the City items
• NOTE: The remaining options are shown in the next section (below)

Expand or Collapse to Specific Level

Instead of changing pivot items individually, you can use the pivot table commands, to expand or collapse the details to a specific level.

1. Right-click the pivot item, then click Expand/Collapse In this example, I right-clicked on Boston, which is an item in the City field.
2. Select on of the Expand/Collapse options:
   • To show details below the selected level, click Expand To [Field Name]. In this example, clicking on Expand to "Category" will expand Boston, and other expanded City items, to the Category level. Cities that are currently collapsed will not be expanded.
   • To hide details below the selected level, click Collapse To [Field Name]. In this example, clicking on Collapse to "Region" will collapse the East region to the Region level. Other regions will not be collapsed.

Expand or Collapse Entire Pivot Table

Unfortunately, there is no command that will expand or collapse the entire pivot table. You can use two steps to expand everthing in the Row Labels area, and the same two steps for the Column Labels area.

In the Row Labels area:

1. Right-click on one of the headings in the outermost field, click Expand/Collapse, then click click Expand Entire Field or Collapse Entire Field.
2. Right-click on one of the headings in the outermost field, click Expand/Collapse, then click click Expand To or Collapse To, for the lowest level field.

Repeat these two steps for the Column Labels area, if necessary.

Macro to Keep One Item Collapsed

Use this macro to prevent a specific pivot item from expanding in a pivot table. Modify the code, to use the pivot field and pivot item names in your pivot table.

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.

NOTE: This technique does NOT secure the Crackers data. People could work around this restriction in many ways

Macro to Keep One Pivot Item Collapsed

Add this code to the pivot table's worksheet code module. Modify the code, to use the pivot field and pivot item names in your pivot table

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

Video Transcript

Here is the full transcript of the video shown at the top of this page: Video: Expand and Collapse Pivot Fields

Introduction

When you first create a pivot table and add fields to the layout, everything is visible. In this pivot table, you can see that I've got four fields in the rows area and one value field.

In this cell, I'm going to use a formula just so we can keep an eye on the pivot table total. So I'll type an equal sign (=) and then scroll down to the very end, and click on the grand total.

Press enter: that puts a GETPIVOTDATA formula in here, and we're just getting the total quantity and I'm going to format it with a comma, so it's easier to read and get rid of those decimal places.

So there's our number, and we can see what's happening in the pivot table, just by keeping an eye on that total.

Filter Pivot Table Category

Instead of seeing everything, you might want to focus on particular regions or products, so you can apply filters.

In this one, I'm going to click on the category, so this is a category called Bars.

I'd like to only show a couple of categories, so in the dropdown I'm going to remove the Crackers and Snacks categories. Now we can see just two, and our grand total has gone down.

Hide Pivot Table Details

Another way to focus in a pivot table, is instead of filtering items out, just hide the detail. So with the bars, maybe I don't need to see the detail on all those different types of bars, but I'd like to keep the total there unchanged.

So one way to do that is to double-click on that item: that hides its details, but it didn't change the subtotal. All the detail is still in there, it's just hidden away for now.

You'll see that it also hid the detail for the New York bars and Philadelphia. So that item throughout the pivot table is now collapsed. We can't see its detail.

If I want to see it again, I can double-click and that brings it back throughout the pivot table. So that's collapsing and expanding a particular item.

Expand & Collapse Buttons

Another way to do that is with little plus and minus buttons. Those aren't visible in my pivot table.

I can show them, if I select a cell in the pivot table, and on the ribbon under PIVOTTABLE TOOLS, I'll click ANALYSE, click the drop-down for Show, and here's the +/- Buttons.

I'll click that, and this toggles it on and off so if I clicked it again, it would hide them, click it again to show.

So with these buttons, we could be doing the same thing as double-clicking. So here it's a minus, so if I click that it collapses; if I click the plus sign, it expands.

Right-Click Commands

We can also use commands on the right-click. So if I right-click on Boston, and point to Expand/Collapse, I can collapse the entire field; so that's going to collapse all the cities.

So all the cities now just show the city name and the subtotal, so we've collapsed it to this level.

If we want to expand, we can use that right-click command again. So I'll right-click on Boston, Expand/Collapse, and I'll expand it to Category.

So that only shows the Boston categories. It left all of these other cities collapsed.

Expand Entire Field

If I want to see the same thing for all the cities, I can expand that whole field. So I'll right-click on Boston, Expand/Collapse, and Expand Entire Field. So now all the cities are expanded, and we can see that last level that we expanded to a minute ago.

And if I want to see this last level, I can right-click on one of the cities, Expand/Collapse, and now I'll expand the pivot table to product. So now we can see all the product detail under every category and city and region.

Expand Entire Pivot Table

So if you're going through the pivot table, and hiding some things, and showing other things; eventually you might want to get it all back to its original condition, of showing everything fully expanded.

You can do that with two steps.

1. The first step in showing everything will be to expand to the lowest level field, which is product. So I'll right-click on a label, (it doesn't matter which label) Expand/Collapse and expand to product.
2. And then we just have to go to the top field, the outermost field, which is region. So I'm going to right click on one of the region labels, Expand/Collapse, and Expand Entire Field.

So that puts everything back, because we've shown the detail right down to the lowest level field, and then expanded the top level field

Return to Video: Video: Expand and Collapse Pivot Fields

Get the Excel Files ✅

No Macros: Click here to download the Expand and Collapse sample file. The file is zipped, and in xlsx format, with no macros.

Macro to Keep Collapsed: Click here to download the file with the Keep One Pivot Item Collapsed macro. The Excel file is zipped, and in xlsm format, and contains macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Last updated: September 13, 2024 3:33 PM