Excel: Replace Blanks in a Pivot Table with Zeros

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: When I have no sales of a particular product in a particular region, Excel leaves those cells in the pivot table blank. This seems like a really bad idea. I've learned in this book that if my data has blanks instead of zeros, Excel will assume that a column is a text column. It is really ironic that Microsoft would dare to use a blank cell in the middle of numeric results.

  1. Annoying and ironic that Excel uses blanks here.

    Strategy: Follow these steps:

    1. Right-click any cell in the pivot table and choose Pivot Table Options.
    2. In the PivotTable Options dialog, select the Layout & Format tab and enter 0 in the For Empty Cells Show text box. Click OK.

  2. Add a zero to the For Empty Cells Show text box.

    Results: Blanks in the values section of the pivot table are shown as zeros.

    Additional Details: You can enter anything in the For Empty Cells Show text box. Some people like to use -- or n.a. in the formerly blank cells. Either works just as well as a zero.