Mrinmoy Roy
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.
Download Practice Workbook
You are recommended to download the Excel file and practice along with it.
2 Quick 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 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. 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, 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:
Read More:Â Excel Pivot Table Formatting (The Ultimate Guide)
Similar Articles
- Reverse Pivot Tables – Unpivot Summary Data
- How to create a pivot table report in Excel
- Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
- How to Create Pivot Table Data Model in Excel 2013
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.
Read More: How to Modify an Excel Pivot Table
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.
Read More: How to Create Pivot Tables for Meaningful Data Analysis!
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.
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. Please visit our website ExcelDemy to explore more.
