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:
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:
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:
① 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.
④ After that, you will see two filter options above the pivot table.
⑤ Now, click on the dropdown from the Quantity. Then, select the Select Multiple Items checkbox.
⑥ Now, from here, uncheck the zero.
⑦ 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:
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.
- 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
① First, clear the FIlter field if you have created it from the previous method.
② Then, select any element from the Row Labels.
③ After that, right-click on your mouse. Then, from the options, select Filter.
④ Now, select Value Filters from the Filter options.
⑤ Now, first, select the column. We are choosing the Quantity column. Then, select does not equal to from the dropdown menu.
⑥ In the next empty field, type 0 (Zero).
⑦ After that, click on OK.
⑧ 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:
As you can see, we are successful to 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.
Just follow these simple steps below:
① First, select the entire table.
② 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.
④ After that, click on OK.
As you can see, there are no zero values in the pivot tables. So, we are successful after using the format cells method.
💬 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.
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!
- 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)