How to Show Zero Values in Excel Pivot Table: 2 Pro Tips

If you have blank cells in your dataset, they will also be represented as blank in your pivot table. To show zero in lieu of blank values in the pivot table, you have to tweak settings first. There are two ways available so far to show zero values in the pivot table. In this article, you will get to know the two methods to display the zero values in the pivot table as well as some additional pro tips about the pivot table in Excel.


Download the Practice Workbook 

You are recommended to download the Excel file and practice along with it.


2 Ways to Show Zero Values in Excel Pivot Table

In this article, we will be using a sample monthly income-expense list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

Dataset for 2 Ways to Show Zero Values in Excel Pivot Table

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Using Pivot Table Options

In the picture below, we can see that there are 3 blank cells marked by orange color. They were actually blank within the raw dataset. As we’ve converted the dataset into a pivot table, those blank cells remained blank as well.

As the data table is all about currency. So we want to show those blank cells containing $0 instead of just blank cells.

To display zeros instead of keeping the cells blank, all we need to do is,

❶ Select any one of the cells first.

❷ Then right-click on it.

At this point, a pop-up menu will appear. This is the pop-up menu,

❸ Select the PivotTable Options.

PivotTable Options Selection

After hitting the PivotTable Options, a dialog box as in the picture below will appear. Here you have to tweak settings a little bit. That’s it.

❶ Select Layout and Format first.

Then under the Format option, you will see a checkbox named For empty cells show. All you need to do here is,

❷ Insert Zero (0) within the box.

❸ Then hit the OK command.

Usage of PivotTable Options to show zero values

As you are done with all the steps mentioned above, you will see zeros appeared instead of blank cells as in the picture below:

zero values in the excel pivot table displayed

📓 Note

If you set the data type of your numerical values as Accounting, then it will show up as dash (-) instead of zero (0). So keep that in mind.


2. Using Advanced Options

Another way that you can use to display zero values in the pivot table in Excel is to use the advanced options. The following steps will guide you to display zero values in the pivot table.

❶ First of all, go to the File option.

❷ Then go to Options.

❸ After that, select Advanced from the options menu.

❹ Under the Advanced option go to Display options for this worksheet.

❺ Under this options mark tick on the Show a zero in cells that have zero value.

❻ Then hit the OK command.

Advanced options selection in Excel

When you are done with all the steps mentioned above, you will see your blank cells are filled with zeros instead of absolute blank spaces.

display zero values in excel pivot table using advanced options


Hide Zero Values in Pivot Table

If you proceed in the opposite direction of the methods that we have discussed in the previous sections, then you will end up hiding zero values in the pivot table in Excel. But for your convenience, you are showing you those two methods backward once again. Let’s go,


1. Pivot Table Options

All you need to do is,

❶ Go to the PivotTable Options once again.

❷ Select Layout and Format.

❸ Leave the For empty cells show box blank.

❹ Hit the OK command.

Hide zero values using PivotTable Options


2. Advanced Options

To hide the zero values in the pivot table in Excel,

❶ Go to File ▶ Options ▶ Advanced.

❷ Under the Display options for this worksheet field, uncheck Show a zero in cells that have zero value.

❸ Finally, hit the OK command.

Usage of Advanced options to hide zero values in Excel

This is all you need to do to hide the zero values once again.


Things to Remember

📌 Make sure that the data type of your numerical values is not set to Accounting.


Conclusion

To sum up, we have demonstrated showing and hiding zero values in the pivot table in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Read More

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo