How to Hide Zero-Value Rows In A Pivot Table

How to Hide Zero-Value Rows In A Pivot Table

Easily hide zero value rows in a Pivot Table


Hiding the rows that contain zero values in a Pivot Table is a common need that many people struggle with. There is a surprisingly simple (but not so obvious) solution.

From the Pivot Table Field List, drag the field that contains zero values from the ‘Choose fields to add to report’ section down to the Report Filter area. Now, in addition to that field being in the Values area, it is also added to the top of the Pivot Table as a Report Filter;

Easily hide zero value rows in a Pivot Table

In the Pivot Table, click the arrow to the right of the Report Filter dropdown, check ‘Select multiple items’, uncheck the 0 value and click OK.

Easily hide zero value rows in a Pivot Table

Now the rows containing zero values in the Pivot Table have been hidden.

Easily hide zero value rows in a Pivot Table

How to Hide Zero-Value Rows In A Pivot Table

3 thoughts on “How to Hide Zero-Value Rows In A Pivot Table

  1. That’s fine where there are no line items at all, but what about where there are negative values and positive values that when summarised by the PivotTable result in a zero value? The stated method won’t work.

    However, I’ve found it’s possible to create an ordinary Filter across the pivot results. If you highlight the cells from the Cell above the Values Column heading down to below the last value, and right-click, you can select the ‘Filter’ option and ‘Filter by selected Cell’s value’. This allows you to omit the zero totals. It creates a one-column Auto Filter. As long as you don’t insert new columns, and as long as the length of your Filter is always as long or longer than the max height of the pivot table, the Filter will always recalculate when you modify your Pivot filters.

  2. Brad’s suggestion works great, except that the “grand total” value at the end of the pivot table does not appear to update as a result of the filter. In my example, I have negative subtotals that I want to filter out, so I took Brad’s steps above and filtered out anything less than/equal to zero. When I go down to the grand total row, the amount is the same before and after applying the filter.
    Any ideas on how to get the grand total amount to update?

Leave a Reply

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