Often you may want to hide zero values in a pivot table in Excel.
Fortunately this is easy to do by using the pivot table Filter function.
The following step-by-step example shows how to use this function to hide zero values in a pivot table in practice.
Step 1: Enter Dataset
First, we’ll enter the values for a dataset that contain information about points scored by basketball players on various teams:
Step 2: Create the Pivot Table
Next, let’s create the following pivot table that summarizes the sum of the points for each team:
Step 3: Hide Zeros in Pivot Table
Suppose we would like to hide the row that contains a value of 0 in the Sum of Points column in the pivot table.
To do so, we can right click on any of the values in the Row Labels column, then click Filter, then click Value Filters:
In the new window that appears, choose Sum of Points, then does not equal, then type 0:
Once you click OK, any row with a value of zero in the Sum of Points column of the pivot table will be hidden:
Notice that the row with the Warriors has been hidden from the pivot table since the value in the Sum of Points column for that team was zero.
If you would like to view the rows with zero values again, simply click the filter icon next to Row Labels, then click Clear Filters From “Team”:
This will remove the filter from the pivot table and the rows with zero values will be visible once again.
The following tutorials explain how to perform other common operations in Excel: