Pivot Tables > Errors

Change Pivot Table Error Values

If there are errors in an Excel pivot table source data, such as #N/A, you might see those errors in the pivot table Values area. See how to hide those error values, or change them to a different value.

Note: For help with other errors, such as "PivotTable field name is not valid" error messages, go to the Troubleshoot and Fix Excel Pivot Table Errors page.

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 range, you might see errors in the pivot table Values area.

In the Excel workbook 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 of the data table – Total Sales.

Note: You can see the VLOOKUP formula in the Formula Bar.

• =VLOOKUP(D5,PriceLookup,2,FALSE

In the PivotTable report that’s based on this Excel table data source, Total Sales is in the Values area.

• The start of its pivot table field name appears in cell A3, as "Sum of TotalSales". The column is narrow, so part of the name is cut off.

In column D (West), and column E (Grand Total), you can see the #N/A 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)

Then, after you fix the errors in the source data, go back to the pivot table, and refresh it. The errors should disappear from the Values area.

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 pivot table error setting, follow these steps:

• Right-click any cell in the pivot table, and click PivotTable Options
• On the Layout & Format tab, go down to the Format section
• Add a check mark to the setting, “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 the OK button, to close the PivotTable Options window.

After you close the dialog box, the errors in the pivot table values will change automatically, to show the text that you entered. You do not need to refresh the pivot table

Values Area Only

This Pivot Table option setting only affects cells in the Values area of the Microsoft Excel pivot table.

• If error values appear in the Row Labels, Column Labels, or Report Filter area, they WILL NOT be replaced.
• Also, any errors on the worksheet are not affected by this setting – you could use IFERROR around those formulas, to hide errors.

The screen shot below shows a #DIV/0! in cell H5 on the worksheet, and it has not been changed to N/A

However, the errors in the pivot table Values area, including the Grand Total row and column, have changed to the new Error format setting.

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 any macros.

More Tutorials

Fix Pivot Table Errors

Pivot Table Options

Data Fields

Show Text Values