totn Excel 2003 Pivot Tables

MS Excel 2003: Change how Empty Cells are Displayed in a Pivot Table

This Excel tutorial explains how to change the display of empty cells in a pivot table in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, I don't want to see empty cells in a pivot table. How do I replace all empty cells with another value?

Answer: First, let's explain what an empty cell is. In the example below, we have order # 10253 that does not have any quantity (row 10 in spreadsheet). Therefore, the quantity is showing as a blank cell.

Microsoft Excel

We'd like to replace all empty cells with the value "n/a". To do this, select any cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

Microsoft Excel

When the PivotTable window appears, check the checkbox called "For empty cells, show". Then enter the value that you wish to see in the pivot table instead of the empty cell. Click on the OK button. In this example, we want all blank cells to show as "n/a".

Microsoft Excel

Now when we return to the pivot table, we see "n/a" as the quantity value for order #10253.

Microsoft Excel