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:
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.
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.
As you are done with all the steps mentioned above, you will see zeros appeared instead of blank cells as in the picture below:
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.
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.
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.
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.
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.
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.