How to Replace Blank Cells with Zeros in Excel Pivot Tables

Watch Video – How to Replace Blank Cells 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:Replace Blank Cells with Zeros Pivot Tables - Data

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

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

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.Replace Blank Cells with Zeros Pivot Tables - PT 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.Replace Blank Cells with Zeros Pivot Tables - Enter 0
  • Click OK.

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

Replace Blank Cells with Zeros Pivot Tables - PT with 0

You can also play around with the formatting of the zeroes. Here is a good tutorial by Mike from Data Pig Technologies. He shows how to apply a gray color to zeroes to subdue its presence.

You May Also Like the Following Excel Pivot Table Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)