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.

How to Hide Zero Values in Pivot Table in Excel?

Implementation

Let’s take a dataset as an example to understand How to Hide Zero Values in a 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.

How to 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: Choosing Pivot Table from the Dataset

First, choose the pivot table you built from the dataset. The pivot table fields pane will now appear on the right side.

Step 2: Fill the Filters Field

Now drag the score and medal count field from the pivot tables field to the filters field.

Step 3: Filter Choices will Appear

Following that, two filter choices will appear above the pivot table.

Step 4: Click OK after Selecting Mulitple Items

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: Result

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.

How to Hide Pivot Table Zero Values Using Excel’s Format Cells Command

Step 1: Select the table

First you need to select the entire table then press Ctrl+1.

Step 2: Select Custom Category in Number Section

Now the format cell dialog box appears. Here select on the custom category.

Step 3: Enter in the type field and then Click OK

After that clear the general text from the type field and write ‘0;-0;;@’ in the text field and then click OK.

Step 4: Result

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.

How to Hide Zero Value Rows in Pivot Tables by Using Filter Function

Step 1: Choose value filters for the desired Element

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: Select Sum of Score in Value Filter box

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: Select “does not equal” and “0” and then Click OK

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: Result

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.

Summary

In conclusion, learning how to hide zero values in Excel pivot tables makes your data look cleaner and more professional. We’ve covered several easy methods, like adjusting settings, using filters, and applying custom formulas. With these tools, you can improve how you present your data in Excel. Practice these techniques to manage and display your data more effectively in your pivot tables.

How to Hide Zero Values in Pivot Table in Excel – FAQs

How to hide 0 values in PivotTable?

To hide zero values in an Excel PivotTable:

• Right–click in the PivotTable
• Select “Value Filters“
• Choose “Does Not Equal” and then enter “0“.

This will filter out all zero values.

How do I remove zeros from a PivotTable average?

To remove zeros from a PivotTable average in Excel, use a calculated field.

• Right-click the PivotTable, select “Calculated Field”
• Create a formula like ” =IF(Value=0, NULL, Value) ” to exclude zeros from the average calculation.