Q: When I have no sales of a product in a particular region, Excel leaves those cells in the pivot table blank (Fig. 1). If my data has blanks instead of zeros, Excel assumes that a column is a text column. How do I get rid of the blanks so my calculations are not affected?
A: 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 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 (Fig. 2).
The result is that the blanks in the values section of the pivot table are shown as zeros. If you don’t like using zeros, you can enter anything in the For Empty Cells Show text box. Some people like to use dashes ( — ) or n.a. in the formerly blank cells. Either works just as well as a zero.
If you would like to submit a question to Bill “MrExcel” Jelen, go to CFO’s Spreadsheet Community Center at www.cfo.com/spreadsheets.