Hide Pivot Table Errors

When there are errors in the pivot table source data, you might see errors in the pivot table Values area. In the screen shot below, a VLOOKUP formula in column E has returned an #N/A error, because the product wasn’t found in the lookup table. That also creates an error in column G – Total Sales.

In the pivot table that’s based on this data, Total Sales is in the Values area, and you can see the errors in the Paper row.

Fix the Errors

If possible, fix the errors in the data, so they don’t show up in the pivot table. In this example, you could use an IFERROR function with the VLOOKUP formula, to return a zero, instead of an error, if the cost can’t be found.

=IFERROR(VLOOKUP(D5,PriceLookup,2,FALSE),0)

Hide the Errors

If you can’t fix the source data, it’s possible to hide the errors in the pivot table. In the pivot table options, you can change a setting, to hide those errors, and replace them with a space character, or other text.

To change the setting:

• Right-click any cell in the pivot table, and click PivotTable Options
• On the Layout & Format tab, add a check mark to “For error values show”
• In the box, type the text that you want, instead of the errors. For example, type a space character, or “N/A”
• Click OK, to close the Options window.

The errors in the pivot table values change, to show the text that you entered.

Note: This setting only affects cells in the Values area of the pivot table. If error values appear in the Row Labels, Column Labels, or Report Filter area, they won’t be replaced. Also, the errors on the worksheet are not affected by this setting – you could use IFERROR around those formulas, to hide errors.

You can find more pivot table formatting tips on my Contextures website: http://www.contextures.com/excel-pivot-table-format.html

Video: Hide Pivot Table Errors

Watch this video to see how to see how to change the setting, and also see how to hide zeros on the worksheet.

_____________________