Table of Contents
The process of hiding zero values in a pivot table in Excel involves using the “Field Settings” option to customize the display of values. By selecting the “Hide Values” option for cells that contain zero, users can effectively remove these values from the pivot table. This can be beneficial for creating a cleaner and more visually appealing table, as well as for more accurately representing data without the distraction of zero values.
Excel: Hide Zero Values in Pivot Table
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:
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: