Remove Blanks, Errors And Zeros In Pivot Tables

📗 Download Video Workbook: »» cutt.ly/up4excel023-2
🎯 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.
👨‍🏫 On-Screen Tutor Version: »» • Say Goodbye to Messy P...
📒 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.....
🅾 SUBSCRIBE NOW 🅾 cutt.ly/Up4ExcelSub
======================================================================================
📗 Download Workbook: »» cutt.ly/up4excel023-2
💥 Shortcuts Cheat Sheet: »» www.up4excel.co.uk/shortcuts
🔓🔑 Remove Excel File Open Passwords: »» • Excel File Locked? Lea...
🎁 Your small gift will help me make better videos for you and others.
Thank You: www.paypal.me/Up4Excel
======================================================================================

[@Up4Excel]

✅Reduce your working hours with my personal coaching. 👉Details at ml.up4excel.com/htcapp04 - Offer Ends 19th August 2022

[@ygmr1993]

Took me ages to find this thanks!

[@Up4Excel]

Glad you did, thanks for commenting 👍

[@mariemcknight1703]

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

[@Up4Excel]

Very glad to help you finally figure it out Marie 👍

[@johnborg5419]

Thanks John. Error Values in Pivot Tables.......that's a drag.

[@Up4Excel]

Indeed it is John....this is the solution 😎

[@wayneedmondson1065]

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

[@Up4Excel]

Thanks for watching Wayne 👍

[@surabhijagadish5210]

Thanks. I have been trying to figure out this.

[@Up4Excel]

Glad you found your answer. Thanks for your comment. 👍 John

[@Up4Excel]

👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[@mrmusicloverable]

First to watch. Thank you.

[@Up4Excel]

Hope you enjoyed it 👍