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 by using conditional formatting in the Pivot Table.

You May Also Like the Following Excel Pivot Table Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster