Skip to main content

How to Replace Blank Cells with Zeros in Excel Pivot Tables

For example, suppose I have a data set as shown below:

Replace-Blank-Cells-with-Zeros-Pivot-Tables-Data.png

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:

Replace-Blank-Cells-with-Zeros-Pivot-Tables-PT.png

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.

Replace-Blank-Cells-with-Zeros-Pivot-Tables-PT-highlight.png

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:

  1. Right-click any cell in the Pivot Table and select Pivot Table Options.Replace-Blank-Cells-with-Zeros-Pivot-Tables-PT-Options-1.png
  2. 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.
  3. If you want to can replace blank cells with text such as NA or No Sales.Replace-Blank-Cells-with-Zeros-Pivot-Tables-Enter-0.png
  4. Click OK.

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

Replace-Blank-Cells-with-Zeros-Pivot-Tables-PT-with-0.png

Back to top