Change Pivot Table Error Values

When there are errors in the pivot table source data, you might see errors in the pivot table Values area. See how to hide those error values, or change them to a different value.

Video: Change Error Values

Watch this video to see how to hide pivot table error values, or change them to a different value. The written steps are below the video.

Error Values

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, to hide the error values
  • Or, type N/A, to show that information is not available
• Click OK, to close the Options window.

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

Values Area Only

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, any errors on the worksheet are not affected by this setting – you could use IFERROR around those formulas, to hide errors.

Get the Sample File

To follow along with the video and written steps, you can download the Change Pivot Table Error Values sample file. The zipped file is in xlsx format, and does not contain macros.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

More Tutorials

Pivot Table Errors

Pivot Table Options

Data Fields

Show Text Values