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

Get FREE Advanced Excel Exercises with Solutions!

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.

Pivot Table Show Zero Values


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:

Creating Pivot Table with Blank Cells

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.

Showing Blank Cells in Dataset

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.

Selecting PivotTable Options

  • 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.

Applying Layout & Format Settings

  • 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:

Output of Pivot Table Showing Zero Values

Read More: Excel Pivot Table Formatting (The Ultimate Guide)


Similar Articles


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.

Showing Blank Cells in Dataset

The following steps will guide you to display zero values in the pivot table.

  • First of all, go to the File option.

Opening File Tab from Excel Ribbon

  • Then, go to Options.

Choosing Excel 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.

Preparing Advanced Settings to Show Zero Values

  • 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.

Output of Applying Advanced Option

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).

Inserting Zero Values in Original Dataset

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.

Applying Layout & Format Settings to Hide Zero Values

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.

Applying Advanced Settings to Hide Zero Values

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.

Output of Accounting Format

  • 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.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo