Remove Blanks, Errors And Zeros In Pivot Tables

🎯 Get rid of errors, blank cells, and zero line items in pivot tables to make your pivot table look good and avoid cluttering your report.

📒 In this Excel PivotTable tutorial you will learn how to delete blank rows, hide blank records, change blank cell to something else, change Pivot Table blank spots to zero, remove the Pivot Table error DIV/0 and remove N/A errors. You can use the standard PivotTable number format option to show zeros as blanks, but if you want to know how to change blank cells in a Pivot Table then you need to watch this video. To change Pivot Table empty cells to zero you use go to PivotTable options where there are options to change Pivot Table blanks to zeros and show any pivotable error as zero, and thereby ensure you remove errors from your Pivot Table. I will also go through how to hide blank records in a Pivot Table in this pivotable tutorial. Rather than delete blank rows in a table, it is much better to hide blank rows which can be done using the Pivot Table filter options.

If we want to get rid of the blank cells and errors there is a pivot table option that deals with both of these things. Clicking anywhere in the pivot table you can right click and select pivot table options. You can see on the layout and format tab there is a box saying ‘for error values show’ and ‘for empty cells show’. We just tick both of those and enter what we want.

It's tempting to put a zero for an error, but if you do that you get a zero but grand total also zeroes so it doesn't become the grand total of that row or column any longer. The only way I found out how to get around this is to go back to the source data and correct for the error, which is far far from ideal. If you're going to do this and you're going to show errors as zeros you need to make sure you're not showing any grand totals anywhere. You don't need to show grand totals so we could just remove the grand totals on pivot table options so we're not showing anything disingenuous and we've not got totals at the bottom. We could then manually total these if we wanted to make it look like the rest of the pivot table.

If we wanted to remove all the rows where everything comes to a zero you can right-click on the pivot table and select filter and then value filters. Select where the sum does not equal zero, as we only want to see lines where the sum of the row does not equal zero. If I do that what that's done is removed any rows where everything has a zero and if I just quickly go down to the bottom you see we've cut several hundred rows out of this pivot table, which is going to make it a lot easier on the eye, and we're not showing any data we no longer need to see.

======================================================================================
#Up4Excel
#Up4PivotTables
#Up4ExcelIntermediate

Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time

Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.

You get new video releases every week, packed full of ways to save time and impress those around you.
💎 Don't miss out and fall behind.....

======================================================================================
🎁 Your small gift will help me make better videos for you and others.

======================================================================================

[mariemcknight]

Thank you so much! I have been trying to figure out how to do this for a few days. Very much appreciated!

[UpExcel]

✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[bertrams]

new here, how do one trace where the error is originating from? and for 0 values in a Pivot table I hit the Comma Icon in the HOME TAB (think it sets the number format to accounting, when you hit the comma, but at least all the Zeros disapear), and then it puts small 'dashes' (im not english) where the values are Zero, much less strain on the eyes that way..

[wayneedmondson]

Nice tips John! Thanks for sharing :)) Thumbs up!!

[surabhijagadish]

Thanks. I have been trying to figure out this.

[johnborg]

Thanks John. Error Values in Pivot a drag.

[UnknownChannel]

Problem with zeros is the data graphs go down to zero and I want it to just show the score for the next score I dont need it going all the way down but skip the 0