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.
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.
In the PivotTable report that’s based on this Excel table data source, Total Sales is in the Values area.
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.
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:
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.
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.
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.
Last updated: August 21, 2022 10:23 AM