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

Get FREE Advanced Excel Exercises with Solutions!

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.

Hide zero values in pivot table


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

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:

Dataset for Hiding Zero Values in Pivot Table in Excel

  • 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.

Inserting Pivot Table from Dataset

  • 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.

Selecting Range for Forming Pivot Table

  • 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)).

Selecting Items for Pivot Table

  • 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 PivotTable Fields.

Inserting Quantity in Filters Option

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

Placing Price in Filters Option

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

Create Filter Field of Pivot Tables and Hide Zero Values

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

Result After Creating Filter Field

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

Selecting Multiple Items on Quantity

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

Deselecting Zero Value in Quantity

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

Result After Hiding Zero Values in Sum of Quantity Column

  • 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.

Result After Hiding Zero Values in Sum of Price Column


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.

STEPS:

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

Picking Any Cell in Pivot Table

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

Picking Filter Function to Filter Values

  • 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.

Placing Condition in Value Filter Option

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

Result After Using Filter Function to Hide Zero Values in Single Column in Pivot Table

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

Inserting Another Condition for Hiding Zero of Another Column

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

Result After Using Filter Function to Hide Zero Values in Single Column in Pivot Table


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. To learn this method just follow these simple steps below:

STEPS:

  • Firstly, select the entire table.

Selecting Range of Pivot 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.
0;-0;;@

Typing Custom Cell Formula at Format Cells Command to hide zero values in pivot table

  • 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.

Result After Utilizing Format Cells Command to Hide Zero Values in Pivot Table


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.

Practise Workbook to hide zero values in pivot table


Download Practice Workbook

Download this practice workbook


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. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Blank in Pivot Table | Pivot Table Formatting | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo