How to Show Zero Values in Excel Pivot Table (2 Quick Ways)

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 how to show zero values in Excel pivot table in 2 quick ways. Along with this, learn some additional pro tips about the pivot table in Excel.

Show Zero Values in Excel Pivot Table: 2 Quick Ways

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 at the dataset:

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

1. Use PivotTable Options to Show Zero Values

In the picture below, we can see that there are 3 blank cells. 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. 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, follow the steps below.

First, select any one of the cells first inside the pivot table.
Then, right-click on it and select the PivotTable Options from the context menu.

After hitting the PivotTable Options, a dialog box as in the picture below will appear.
Here, 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.
Lastly, hit the OK command.

Finally, you are done with all the steps mentioned above.
As a result, you will see zeros appear instead of blank cells as in the picture below:

2. Apply Excel Advanced Option to Display Zero Values in Pivot Table

Another way that you can use to display zero values in the pivot table in Excel is to use the Advanced options. As the data table is all about currency, we will show those blank cells containing $0 instead of just blank cells.

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.
Here, mark tick on the Show a zero in cells that have zero value.
Then, hit the OK command.

Lastly, 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.

Note: Remember that, in the case of the Advanced option, you must have Zero (0) values in the original dataset (as shown below) to create Pivot Table showing Zero (0).

How to 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, we are showing you those two methods backward once again. Let’s go,

1. Hide Zero Values with PivotTable Options

All you need to do is,

First, go to the PivotTable Options once again.
Then, select Layout and Format.
Here, leave the For empty cells show box blank.
Finally, hit the OK command.

2. Use Advanced Option to Hide Zero Values in Excel Pivot Table

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

Initially, 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 format. Otherwise, it will show Dash (–) instead of Zero (0) values like this.

You can type any other Text or Symbol other than Zero to show in your Pivot Table.

Download Practice Workbook

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

Showing Zero Values in Pivot Table.xlsx

Conclusion

To sum up, we have demonstrated in 2 quick ways how to show zero values in Excel pivot table. 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.

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

Get FREE Advanced Excel Exercises with Solutions!