---

MS Excel 2003: How to Handle Errors in a Pivot Table

This Excel tutorial explains how to change the display of errors 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 errors in the pivot table. How do I replace all errors with another value?

Answer: Let's first take a look at an example of an error in a pivot table. This is a picture of our underlying data for the pivot table. You can see in row 16 that the quantity entry has an error in it. This could be the result of a VLOOKUP function, for example.

Now when you take a look at the pivot table that uses this data, you can see the error in the pivot table.

You can replace this error with a more appropriate value. To do this, select a cell in the pivot table. Right-click and then select "Table Options" from the popup menu.

When the PivotTable Options window appears, check the checkbox called "For error values, show". Then enter the value that you wish to see in the pivot table instead of the error. Click on the OK button. In this example, we've decided to replace the error with a 0.

Now when we return to the pivot table, this is what we'll see.