Real Excel Skills - 304 - Removing blank Rows in Pivot Tables

NOTE: To jump right to the answer click here: 7:50
This video deals with how to set up data where you can add lines and update your pivot table without having to update the range the pivot table refers to manually.
Thank you for watching! Please feel free to comment with any questions you may have. Also, if you found this video helpful, please like and subscribe to my channel!
Copyright © 2019 by Jeffrey J. Reale

[@baranisurya5642]

That worked !!! I was stressed out to remove this. You saved me lot of time. Thanks

[@parsingh559]

god tier excel advice

[@ifeanyicharlesokafor9104]

I have been searching for solution to this for months. You just saved me mate. 👍👍👍👍👍👍👍👍

[@WJA5]

This was EXACTLY what I was looking for but could not find a solution! Thank you for putting the time stamp to get to the problem & answer. I would also suggest that you realign the dual screen. Thank you!!!

[@amiraldamiry]

Searched too much for real solution but not found it, You are the Only one have the solution so thanks a lot and appreciate your efforts....

[@JherTee_Gaming]

These are the tricks that I've been looking for in my project

[@maratha9980]

Resize tables, Hide blank data, check for cardinality if there... thnx for this also❤❤❤

[@jasminbanares111]

You solved my problem!!! Been trying to eliminate that (blank) in my dashboard. I rarely leave comments, but you helped me a lot. Thanks a lot!

[@terrywoodfine8848]

Great video mate, explained perfectly.

[@arutalanala8]

You have no ideaaa how much thiss helpss meeee goshh 😭, i was so stressed out bcs of that one blank and finally i came across this video. I'm not the type who likes to write comments on yt but i just want to appreciate ur work. Thank you so much, hope u have an amazing life ahead ✨

[@filmreale]

Thanks so much for the comment! 😊

[@prashantjagtap7710]

Sir you helped sort my real time problem..thanks a ton !

[@shaunfabiano2687]

Perfect solution, thank you!

[@Mani_DS]

Thank you so much for this. Simple solution that helps a tonne!

[@kaimueric9390]

Just go straight to the point in your future videos. If a video is about removing blank rows, most of us come here because that's the only thing they needed. The rest of the stuff, you can create separate videos and potentially get more views than you have from this one.

[@filmreale]

Hey Kaimu! Thanks for the comment! That's actually really helpful feedback; I appreciate it!

[@filmreale]

As a partial solution, I put a time stamp in the description that skips you to the answer.

[@avantikanatasha]

I agree with this comment because I was able to close the video thinking that it's just another clickbait 😁 thanks for the trick 😊

[@debartaghosh8883]

Thanks for this knowledge sharing..it help me real time

[@jametheresareale2897]

Excellent Teacher❤

[@lizz333]

This is absolutely great! Like!

[@filmreale]

Thanks so much! 😄

[@md003ali]

Brief & effective video. Thanks! It works on Pivot columns if data is text its not working if the data is date, as it doesn't show Label filters instead it shows Date filters.

[@filmreale]

I think you should be able to use a date filter that is just greater than 0 essentially. Let me know if that works. Apologies for the slow reply!

[@apsmyjob]

Very useful...tnx

[@ehabomier4759]

Thank you so much ❤ you solved big problem for me now 😅

[@robertdevargas5656]

You are a genius!! problem solve

[@micutzudan10]

Very good, thx

[@taylormccuistion6530]

I know this is old, but I'm just finding it, because I'm just dealing with this problem. But, I also have another problem that's the same but different, if you're still checking responses to this video. This fixed my one pivot table. Thank you so much! But, I have another pivot table where the filter is a nothing for empty row labels. The column is brand names. Some of the data is for fees, which have no brand name associated, so that column is empty. (blank) doesn't show up in the filter list. It's just a checkbox next to nothing. I've tried filtering (blank). I've tried filtering "". I've tried filtering 0. I've tried filtering "0". I've tried filtering a space. I've tried filtering " ". I can uncheck it, obviously. And, I have been. But, I'd like to apply this trick you used here, to automate as much as possible, and minimize my number of steps. But, I cannot figure it out. I can filter anything above 0, and that works. But, I'm worried one day the data will throw me a curve, and that will filter out something I didn't know was there. I'd like to be able to filter out labels that are specifically "nothing".

[@taylormccuistion6530]

Actually, I seem to have figured it out with: Label Filter > is greater than > space Please let me know if you can think of a way this only works this time, and could trip me up down the road.

[@itsdaj]

Awesome explanation and good tips. Much thanks.

[@Alastair-ix8pz]

Problem solved! thank you

[@DisciplinedTraderUK]

Legend!

[@shivadahiya3461]

Thanks buddy❤

[@enuomar]

Good one

[@mortenlanda2211]

Thanks😀

[@abrarshaikh5286]

Thanks for this amazing solution.Can we use the same trick for report filed in the pivot table? I am using Office 2010, and in Report Filed Filter showing only one search bar, not showing these options.

[@filmreale]

Hmmm can you elaborate? If you followed the instructions, the pick list filter will still show "blank" but the actual pivot table won't show it (if that makes sense).

[@jeroenpostma908]

Question, when using a slicer on the pivot table, it does not hold the label filter when selecting on the slicer and then cancel the filter on the slicer, is there a way to make it permanent?

[@Valmorea-tt7vg]

It worked! but what do you need to do when there's a empty row in your pivottable with a value 0.00?

[@somadevnath309]

When "add this data to data model" is checked, this filter doesnot work. Please suggest for that.

[@filmreale]

Hey Soma! Thank you for your question. When I use the add to data model feature, it seems to automatically exclude blanks at the bottom of the dataset. However if I delete some data to force it to see some blanks, I then can use the same filtering I use in the video except I will not type in "(blank)" but rather just leave the field blank. LMK if that works for you!

[@masc1992]

@@filmreale thanks. Leaving it blank worked

[@lucileofvenus]

tysm

[@junedshaikh2964]

Can you replace (Blank) in the Column field with "-"

[@AmericanFlyersDiving]

Is it just me or is the easiest way to see if cells are equal is just =A1=A2351. I don't think you need the '--' in there. What's the benefit for 1/0 vs. True/False?

[@filmreale]

Hi there! Thanks for the comment! It’s true that you don’t need to use the double dash in all circumstances. One reason to use the double dash is because the 1/0 scheme is easier to conditionally format. That’s helpful when you have a large grid or a long line of cells to check since it will be easy to see the zeros (as they’ll be red in a sea of green when conditionally formatted using default settings). I’ve noticed some people prefer to read numbers/text and others prefer visuals and conditional formatting, so it can be a personal preference thing. That aside knowing how to use the “- -“ is helpful as it’s needed in some formulas (particularly when you are using more complex applications of the SUMPRODUCT formula).

[@AmericanFlyersDiving]

@@filmreale Can you give me an example of when it’s needed?

[@brenm2090]

Why not simply ensure the data source only includes data and no blank sections below. Data updates adding extra rows after - would the pivot table pickup extra rows?

[@brenm2090]

And what up with the split screen? Confusing to view this way...

[@adamcarljohnbonilla6651]

katabian nga content creator.

[@francismolina4012]

My Label Filter cannot be click, may I know why

[@matthewv4170]

Why not just stick the data in a table

[@filmreale]

That can also work. I find that table formatting can slow down processing in Excel for large datasets, so I usually avoid them.

[@matthewv4170]

@@filmreale no it doesn't. Excel prefers tables

[@guianadjib]

10 mns talking 1 mn action you stress me up

[@puransingh2343]

The video is not at all clear !!

[@Forever_Young0212]

too much talking!