Hide Pivot Table Detail Without Filtering

To focus on specific data in a pivot table, you can use report filters or field filters.

FilterIntro07

However, using those filters affects the pivot table subtotals and grand totals. Only the amounts for the filtered data are included in the results.

FilterIntro01

Collapse or Expand the Fields

Another option for hiding the pivot table details is to collapse one or more of the items, so only its subtotal is showing. In the screenshot below, I’m about to click on the Collapse button for the Bars category in the pivot table.

PivotTableExpand02

The Bars data is still in the pivot table, but only its subtotal is showing, not the City detail rows. The subtotals for East and Bars are not affected.

PivotTableExpand03

Show the Details Again

After you’re finished focusing on the specific data, you can show all the detail rows again.

A quick way to show all the details for a field is to right-click on an item in the field, such as Bars.

Then, in the popup menu, click Expand/Collapse, and click Expand Entire Field.

PivotTableExpand01  

Watch the Pivot Table Video

To see the steps for hiding or showing the detail items in a pivot table, you can watch this short Excel video tutorial.

Or watch on YouTube: Hide Pivot Table Detail Without Filtering

___________

You may also like...

3 Responses

  1. Kanti says:

    Hi Debra,
    Another useful tool is the use of Slicers

    Cheers
    Kanti

  2. Diabolik says:

    Although the OLD, TREMENDOUS problem of Excel pivots is still there: you cannot distinguish between VISUALIZATION filters and UNIVERSE filters. Hiding=Filtering, there is no option as far as I can see. I was hoping to see this resolved in O2010, but the problem is still there. And, especially if you work with values “displayed as % of…”, this makes Escel pivots totally USELESS

Leave a Reply to Diabolik Cancel reply

Your email address will not be published. Required fields are marked *