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

dataset of hide zero values in pivot table

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.

Now, we are creating a pivot table of this dataset. After that, it will look like this:

pivot table of the dataset

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

1. Create Filter Field of Pivot Table and Hide Zero Values

Now, I use this method often. It 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

First, 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 and Price into the Filter field.

filter field

Create Filter Field of Pivot Tables and Hide Zero Values

After that, you will see two filter options above the pivot table.

Create Filter Field of Pivot Tables and Hide Zero Values

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

 Now, from here, uncheck the zero.

uncheck the zero value

After that, click on OK.

As you can see, there are no zero values in the Quantity column of the pivot table.

Now, do the same process for the Price(Per Unit) column. After that, our pivot table will look like this:

output of hide zero values in pivot table

Finally, you can see that there are no values in the pivot table. So, 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


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

First, clear the FIlter field if you have created it from the previous method.

Then, select any element from the Row Labels. 

hide zero values in pivot table using filter option

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

 Now, select Value Filters from the Filter options.

select value filters

Now, first, select the column. We are choosing the Quantity column. Then, select does not equal to from the dropdown menu.

Hide Zero Values in pivot table

In the next empty field, type 0 (Zero).

After that, click on OK.

output of hide zero values of pivot table

As you can see, there are no zero values in the Quantity column.

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

 After that, it will look like this:

output of hide zero values of pivot table

As you can see, we are successful to 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 don’t want to hide cells, you can certainly use this method.

Just follow these simple steps below:

📌 Steps

First, select the entire table.

Hide Zero Values of Pivot Table Using Format Cells Command

Then, press Ctrl+1 on your keyboard to open the Format Cells dialog box. Next, select Custom.

After that, clear the General from the Type field. Then, paste  0;-0;;@ on the field.

Hide Zero Values of Pivot Table Using Format Cells Command

After that, click on OK.

Hide Zero Values of Pivot Table Using Format Cells Command

As you can see, there are no zero values in the pivot tables. So, we are successful after using the format cells method.

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.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to hide zero values in the 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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo