Do you want a quick way to clean up your pivot table and remove blanks and errors from it? Below, I’ll show you how to do that with just a few steps. In the below pivot table, I have error values and blank row values, which indicate that data is missing:
Ideally, we would adjust our data set to ensure that this data is cleaned and there are no errors. But if you need to quickly clean this up, here’s what you can do.
How to remove error values from a pivot table
To prevent error values from showing on your pivot table, follow these steps:
1. Select your pivot table.
2. On the PivotTable Analyze tab, click on Options
3. Under the Format section, check off For error values show
4. If you want something else to show in place of an error value, enter it in that field. Otherwise, leave it blank and then press OK.
Now your pivot table will not show any error values on it:
There’s still the issue of the (blank) value in the row labels. Let’s address that issue next.
How to remove (blank) row labels from a pivot table
Follow these steps to get rid of the ‘(blank)’ row values which appear in your pivot table:
1. Select the drop-down filter button on your pivot table.
2. Select Label Filters and Does Not Equal
3. Set the criteria so that it does not equal (blank)
This will now remove the blanks from your pivot table:
Now both the blanks and error values are gone from your pivot table.
If you like this post on How to Hide Blanks and Error Values on a Pivot Table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.