How To Replace Blank Cells With Zeros In Excel Pivot Tables

Excel Pivot Table is a great tool. It only takes a few clicks to create a Pivot table and you can easily analyze huge datasets without worrying about human errors. One of the default settings in Pivot Tables is to show a blank cell when there is no data for the given row/column label. In this tutorial, you’ll learn how to replace blank cells with zeroes or any other value in Excel Pivot Tables. Download the Example File

Replace Blank Cells with Zeros in Excel Pivot Tables

For example, suppose I have a data set as shown below: If I use this data set to create a pivot table with Geography in Rows Area, Product in Column Area and Revenue in Values area, the result is something as shown below:

As you can see, there are blank cells in this pivot table. A blank cell indicates that there is no record/activity for that row and column label. For example, here it means that there are no sales of Hardware in Australia and Services sales in the US.

In most of the cases, it is a good practice to show 0 when there is no value/activity in the data used for the pivot table. At a cosmetic level, it looks better and more consistent with all numbers as the data. Excel Pivot Tables has an option to quickly replace blank cells with zeroes. Here is how to do this:

Right-click any cell in the Pivot Table and select Pivot Table Options. In Pivot Table Options Dialogue Box, within the Layout & Format tab, make sure that the For Empty cells show option is checked, and enter 0 in the field next to it. If you want to can replace blank cells with text such as NA or No Sales. Click OK.

That’s it! Now all the blank cells would automatically show 0.

You can also play around with the formatting of the zeroes by using conditional formatting in the Pivot Table.

How to Group Dates in Pivot Tables in Excel. How to Apply Conditional Formatting in a Pivot Table in Excel. Pivot Cache in Excel – What Is It and How to Best Use It. Using Slicers in Excel Pivot Table – A Beginner’s Guide. How to Refresh Pivot Table in Excel. How to Add and Use an Excel Pivot Table Calculated Field.