Specify what your PivotTable displays in place of empty cells and errors (2003/2007/2010)

PivotTables can look incomplete and unprofessional if there are empty cells or error messages. Instead of manually replacing the error values you don’t want to see or placing zeros into blank cells, you can set a quick preference that does the job for you.

To set up specific text for empty cells or error messages:

1. Launch Excel and open the workbook with your PivotTable.
2. Select the PivotTable and make sure the PivotTable toolbar is displayed. If it isn’t, choose View | Toolbars | PivotTable from the menu bar.
3. Click the PivotTable button on the PivotTable toolbar and choose Table Options from the resulting dropdown menu to open the PivotTable Options window. (In 2007, click on the PivotTable button on the PivotTable Tools ribbon, then choose Options.)
4. Select the For Error Values Show check box and enter the text you want to display in its corresponding text box.
5. Select the For Empty Cells Show check box and enter the text you want to display in its corresponding text box.
6. Click OK to apply the settings to your PivotTable.

If you want errors to display as blank cells, simply leave the For Error Values Show text box blank.