How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)

Pivot table is one of the essential tools of Microsoft Excel. If you have worked with that, you might know that. It gives us a detailed view and insight into the dataset. You can perform a lot of tasks with that. You may have zero values in the pivot table. In this tutorial, you will learn to hide zero values in the pivot table with appropriate examples and proper illustrations.

Download Practice Workbook

Download this practice workbook

3 Easiest Ways to Hide Zero Values in Pivot Table in Excel

In the upcoming sections, we will provide you with 3 essential and adequate ways to hide zero values in the pivot table. We recommend you learn and apply all these to your datasets. It will surely enrich your Excel knowledge. Before we start, we are showing you our following dataset:

• Now, we will form the Pivot Table from the dataset.
• To do so, firstly, select the dataset.
• After selection, click on Insert >> Pivot Table >> From Table/Range.

• Instantly, a window named Pivot Table from table or range window will open up.
• After that, in the Table/Range field, you can automatically see the selected range.
• Then, give a tick on New Worksheet and press OK.

• As a result, you can observe the Pivot Table in the newly opened worksheet.
• Then, click on the table and in the right side of the sheet, select these 3 options (Products, Quantity, Price( Per Unit).

• Therefore, our Pivot Table is ready.
• As you can see, there are some zero values in the pivot table.
• Sometimes, you will need to hide these to get a clear look at the pivot table.

As we have zero values in the Quantity and the Price column, we are using these to demonstrate our tutorial.

1. Creating Filter Field to Hide Zero Values in Pivot Table

Naturally, we use this method most often times. Because this method is simple and easy to use. It saves a lot of time. If you want to hide zero values from the pivot table quickly, you can definitely make this your go-to method. We can filter the zero values from the Filter field. Just follow these steps to perform this:

STEPS:

• Firstly, click on the pivot table that you created from the dataset.
• Now, on the right side, you will see pivot table fields.

• Now, from the pivot table fields, drag the Quantity into the Filter field.

• Similarly, from the pivot table fields, drag the Quantity into the Filter field.

• As a result, you will see two filter options above the pivot table.

• Now, click on the dropdown from the Quantity.
• Then, select the Select Multiple Items named checkbox.

• Now, from here, uncheck the zero.
• After that, click on OK.

• As a result, there are no zero values in the Quantity column of the pivot table.

• Similarly, do the same process for the Price(Per Unit) column.
• Finally, our pivot table will look like the following figure.
• Here, you can see that there are no values in the pivot table.
• Therefore, we are successful in using the filter field method of the pivot table.

Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)

Similar Readings

• How to Ignore Blank Cells with Formulas in Excel Chart
• Excel Chart: Ignore Blank Axis Labels (with Easy Steps)
• How to Use XLOOKUP to Return Blank Instead of 0
• Leave Cell Blank If There Is No Data in Excel (5 Ways)
• How to Use VLOOKUP to Return Blank Instead of 0 (7 Ways)

2. Using the Filter Function of Excel Pivot Table

Now, this method also looks like the previous method. You can also use this filter option to hide your zero values in the pivot table. Though I don’t use this method too often, in my opinion, you should also learn this method. Just follow the following simple steps to hide zero values.

📕  Read More: How to Modify an Excel Pivot Table

STEPS:

• Firstly, go to the Pivot Table that we have created.
• Then, select any element from the Row Labels. 
• Here, we have selected Butter.

• After that, right-click on your mouse.
• Then, from the options, select Filter >> Value Filter.

• Now, select the column.
• Here, we are choosing the Quantity column.
• Then, select does not equal to from the dropdown menu.
• After that, type 0 (Zero) in the following empty field.
• Then, click on OK.

• As a result, there are no zero values in the Quantity column.

• Similarly, do the same process for the Price column if you want.

• As a result, it will look like the following figure.
• Therefore, we successfully hide zero values in the pivot table efficiently.

Read More: How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)

3. Hide Zero Values of Pivot Table Using Format Cells Command in Excel

Now, this method is a little bit tricky. You won’t see people using this method too often. Though this method won’t hide the cells with zero values, you can learn this method. It just hides the zero values from the cells. So, if your goal is to hide zero values but doesn’t want to hide cells, you can certainly use this method. To learn this method just follow these simple steps below:

STEPS:

• Firstly, select the entire table.

• Then, press Ctrl+1 on your keyboard to open the Format Cells dialog box.
• Next, go to the Custom panel.
• After that, clear the General from the Type field.
• Then, Paste the following code on the field and press OK.

• As a result, there are no zero values in the pivot tables.
• So, we have been able to successfully hide zero values in the pivot table.

Read More: How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)

Things to Remember

• The second method will hide zero values from a particular column, not from the whole pivot table.
• The third method will hide zero values, not the cells or rows.

Practice Section 

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to hide zero values in pivot table in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website ExcelDemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Hide Chart Series with No Data in Excel (4 Easy Methods)
• Show Dash Instead of Zero in Excel (4 Easy Methods)
• How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
• Excel IFERROR Function to Return Blank Instead of 0
• How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)
• Remove Zeros in Front of a Number in Excel (6 Easy Ways)