• Excel Book Excerpt

Excel Replace Blanks in a Pivot Table with Zeros

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

Replace Blanks
in a Pivot Table with Zeros

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.


Figure 784 Annoying and ironic that Excel uses blanks here.

Strategy: When pivot tables first came out, there was no way to correct this problem. After much outcry from accountants everywhere, Microsoft gave us a way to solve the problem. Follow these steps:

1. Select one cell in the pivot table in order to display the PivotTable ribbon tabs. On the Options tab, click the Options icon.

2. In the PivotTable Options dialog, select the Layout & Format tab and enter 0 in the For Empty Cells Show text box. Click OK.


Figure 785 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy