One of Microsoft Excel’s most important features is the pivot table. You might be aware of this if you have worked with it. It provides us with a thorough view and insight into the dataset. You can do a lot with it. The pivot table might include zero values. In this lesson, you will learn how to hide zero values in the pivot table using relevant examples and visuals. Hide the rows in a pivot table that have zero values. After concealing the zero-value rows, your pivot table will seem neater and the data will be suppressed.
Implementation
Let’s take a dataset as an example in order to understand How to Hide Zero Values in Pivot Table in Excel. This is the dataset that we are going to use in this scenario.
The excel table has some zero values, as you can see. First, you must transform an excel table into a pivot table. You may need to hide them at times to have a clear view of the pivot table.
Methods for Hiding Zero-Values
There are three different methods for hiding zero values in an Excel pivot table.
- Create a Filter field to hide rows with zero values.
- Hide zero-value rows in pivot tables by using the Filter function.
- Hide Pivot Table Zero Values Using Excel’s Format Cells Command.
Method 1: Create a Filter Field to Hide Rows with Zero Values.
Please follow the procedures below to prevent the zero-value rows from being displayed in the pivot table:
Step 1: First, choose the pivot table you built from the dataset. The pivot table fields pane will now appear on the right side.
Step 2: Now drag the score and medal count field from the pivot tables field to the filters field.
Step 3: Following that, two filter choices will appear above the pivot table.
Step 4: Then, in the score field, click the drop-down arrow and select the Select Multiple Items boxes, then uncheck the value 0 and then click on OK.
Step 5: As you can see, there are no zero values in the pivot table’s score column. Repeat the process for the Medal count column. Our pivot table will then look like this:
Finally, the pivot table shows that there are no zero values. So we were successful in using the pivot table’s filter field technique.
Method 2: Hide Pivot Table Zero Values Using Excel’s Format Cells Command.
Step 1: First you need to select the entire table then press Ctrl+1.
Step 2: Now the format cell dialog box appears. Here select on the custom category.
Step 3: After that clear the general text from the type field and write ‘0;-0;;@’ in the text field and then click OK.
Step 4: As you can see, there are no zero values in the pivot table.
Finally, the pivot table shows that there are no zero values. So we were successful in using the format cells command.
Method 3: Hide Zero Value Rows in Pivot Tables by Using Filter Function.
Step 1: To begin, choose any element from the Row Labels. Then right-click on your mouse and then, from the menu, choose Filter and then choose Value filters.
Step 2: Then the Value filter dialog box appears. Now first select the column from the drop-down, here we are selecting the sum of the score.
Step 3: Now in the second field select does not equal from the drop-down and then in the third field write 0. Click OK.
Step 4: As you can see, the pivot table’s score column has no zero values. Repeat the procedure for the Medal count column. Then our pivot table will look like this:
Finally, the pivot table shows that there are no zero values. So we were successful in using the filter function.