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.
- Annoying and ironic that Excel uses blanks here.
Strategy: Follow these steps:
- Right-click any cell in the pivot table and choose Pivot Table Options.
- In the PivotTable Options dialog, select the Layout & Format tab and enter 0 in the For Empty Cells Show text box. Click OK.
- 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.