Say Goodbye to Messy Pivot Tables: Fix Blanks, Errors, and Zeros with Ease!

[@waseemraja9988]

Thank you, that was very informative and helpful, I was very annoyed by these zero values and errors.

[@Up4Excel]

They can certainly be annoying. Glad it was helpful Waseem 👍

[@thechelz02]

JUST what I needed, thank you so much for your video!

[@Up4Excel]

You're so welcome! I'm glad it helped you 👍

[@hkay8929]

Thank you!! That was very useful

[@Up4Excel]

Glad it was useful. Have fun with your pivot tables 👍

[@asmaajittou9045]

Thank you so much for this video ! I've a case, after pivot i'd like to remove all the blanks just by column without removing rows (so in this case the columns ll not have the same length) is that possible ? there are no relations between columns in same rows( not same id)

[@Up4Excel]

Not sure that is possible in a pivot table as all columns will always be the same length. I'd imagine you could do something with formulas though.

[@wayneedmondson1065]

Thanks for the video.. great tips. Thumbs up!

[@Up4Excel]

Thanks Wayne

[@RohitKumar-cc7zi]

Yes its a great resolved by you, thank a lot just keep it up, weldone 👍👍

[@Up4Excel]

Glad it helped. Thanks for the support 👍

[@MotoEnz]

I subbed coz i got what i need to know thank you Sir...

[@Up4Excel]

Glad I could help.... hope you get more from my other videos too 👍

[@allarousan8041]

Hi, How can I exclude zero values out when calculating the avg in pivot table?

[@Up4Excel]

I can't test this at the moment, but my guess is to use a 'value filter' on the pivot table to exclude zero values. If your averages change them it's worked 👍 If it doesn't them you'll need a calculated field or use power pivot which are both to much to explain in a comment I'm afraid. Good luck

[@vijayprajapati6985]

Thanks for this video❤️

[@Up4Excel]

My pleasure, glad to help 👍

[@nataraj6311]

Thanks for the Video..!!!

[@Up4Excel]

You're welcome 👍

[@williamrichard1828]

Hi there. Nice video. I have a s/s pivot where I have a calculated item (being the difference between the forecast last month and this month). For many account codes the forecast hasn't changed (so the calculated item = 0). This might be because there never was a forecast or the forecast did not change. What can I do to show only those accounts where the forecast has changed ie don't show zero values for calcuated items? TIA

[@Up4Excel]

I would think you could just filter by the calculated field total in much the same way as in the video.

[@aped]

Thanks!

[@Up4Excel]

No problem!

[@Up4Excel]

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

[@ThereturofJaffar]

after that i dont want that world Error to be shown or exists in my sheet> (How to remove it) can we replay it by Ctr+F replace? please answer

[@Up4Excel]

You won't be able to find and replace errors in standard cells like that I'm afraid. The reason is they are an output of a formula rather than what is typed. If you change your formula to =IFERROR([original formula],0) then a zero will show instead of the error/ Replace the zero with "" to just hide errors.

[@JamesMathew2023]

Thanks Cool

[@Up4Excel]

Glad you liked it Rahul 👍

[@g0dp3dr0]

thank you - good vid

[@Up4Excel]

Happy to help 👍

[@narakdk]

How to hide blanks when there are many coloumns with a different value reference?

[@Up4Excel]

Well, good question but unless every column is blank you would be hiding valid data in other columns. If you still want to hide them though there is a work around using an Excel hack to add column filters into pivot tables. Click one cell to the right of the pivot table headers (it must be adjacent to the pivot table but not in it), and click the filter button on the data ribbon. This will add filters to all your pivot table columns and you can then filter out blanks one column at a time. Neat rick really 👍

[@Up4Excel]

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. Subscribe for more cutt.ly/ewsz9Sn 📌👍💚

[@entertainmentgalaxy971]

thanks for PT fun

[@Up4Excel]

Any time! Plenty more pivot table fun to be had too 👍

[@Up4Excel]

👉 NEW: 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!

[@emanhamza6257]

There is some cells show that word (blank) how can I remove them?

[@Up4Excel]

Hi, you should be able to use the filter technique from the video and untick the word blank so it won't show.

[@itorres008]

😟Going back to the source data and correcting the errors is not ideal? It is essential. If there are errors in the raw data then the Pivot Table, totals, graphs are useless because we don't know if the error is pennies or thousands of dollars. We can't rely on this to make any decisions. It's like the old saying, "Garbage in garbage out". Suggesting that we get rid of Totals in the Pivot Table to "eliminate" the errors could get you fired from a company. Seen that almost happen when the president of our company went to the board of directors meeting with Financial Statements done in Excel by our accounting department, and at the meeting one of the directors casually looks over them and says "these totals don't add up, there's a mathematical error here...". He came back pissed as his credibility in front of the board was damaged. Also, suggesting that we calculate the totals manually defeats the purpose of using Excel.

[@Up4Excel]

Thanks for your comment and I understand and agree with your points. I certainly didn't mean to imply anyone should cover up genuine errors before understanding the consequences. Your story is a very good example of why. I believe you are right to always correct the source data for genuine errors, although error values in Excel don't always mean incorrect data. For example, DIV0 can occur for genuine reasons and you may want to exclude it from your pivot table. I guess the bottom line is understand your errors before you start hiding any. p.s. When I say manually I mean using the SUM formula outside the pivot table so a bad choice of words by me.

[@SUR-NI-VARA]

How to get PT average Results, ignoring empty, blank, zero totals from source, based to sum value field a next column in PT.

[@Up4Excel]

To ignore blanks etc in averages try the SUM divided by COUNTA or perhaps COUNTIF(A1:A100,"""")

[@mreriiie]

Ok video, but is not fixing the problems as we want the totals at the bottom to function

[@Up4Excel]

Hi, I explain how to get around that in the video here: kzbin.info/www/bejne/hIeQhoWaq7yigdU . It requires changing your source data, but it really is the only way I have found. If you find a better one let me know.

[@charliedodd6998]

useless

[@Up4Excel]

What answer were you looking for Charlie?