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.
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, 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.
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)
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:
The errors in the pivot table values change, to show the text that you entered.
This setting only affects cells in the Values area of the pivot table.
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 my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: October 7, 2020 4:28 PM
Contextures RSS Feed