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 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:
- 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 PivotTable 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.
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.
- 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.
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.
- 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;;@
- 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.
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.
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!
