Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

Show rows with empty cells in Excel Advanced Filtering

Hello,

In Excel (2007), suppose you want to use Advanced Filter to display only rows with blank or empty cells in a particular column.  For example, if your table is four columns wide (A-D), with the headings in Rows 1 & 5 and the Criteria Range is A1:D2 and the List Range is A5:D100, what entry should be placed in Cell B2 so that you will end up with only those rows with empty Col B cells showing (i.e. any rows with an entry in column B will be hidden)?

Thanks
Avatar of xllvr
xllvr
Flag of United States of America image

I may not be completely understanding your example (a sample would be great), but couldn't you just filter on blanks for that particular column?  Apologies if I have misunderstood.
To clarify, you shouldn't need to do an Advanced Filter if you're only looking to hide rows in one column that have cells with data.  If you applied a normal filter, clicked on the drop-down, filtering arrow, you should be able to choose "Blanks" and that will hide everything in Column B containing an entry.  Does that help or have I oversimplified?
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oops...just got there myself.  Teylyn is absolutely correct.  Just use = in the for that column's criterion.  Thanks, teylyn!
Avatar of Steve_Brady

ASKER

>>...just enter =  Nothing else.  ~teylyn

Hi teylyn.  Remember my AAARRRGGGHHH!!! from the other day?  Well, please note that I am repeating it:  AAARRRGGGHHH!!!  Checkout this thread  and you will know why...(sigh).

Oh, and while you are there, can you tell me if there is anything ambiguous or confusing about:

"...what entry in the Criteria area will result in displaying all rows which have empty cells in the corresponding column?"

I'm seriously curious to know if there was any way I could have stated that more clearly.

Once again you have saved me with a short and simple answer!  Many thanks!

BTW, what do I do when I am required to award 500 points to close a thread but I can't -- or at least I don't have any idea where to assign them?


>>xllvr,
Thanks for taking a stab at the answer!  I appreciate your efforts and BTW, making comments like, "Apologies if I have misunderstood." and "Does that help or have I oversimplified?" are very helpful to question askers because it lets us know that you're not absolutely certain about the solution.  Thus even though you gave the wrong answers, you benefited by learning something and I was able to avoid spending a bunch of time to see if your suggestions work!

In way too many instances, Experts (quote-unquote) answer questions in an absolute or dogmatic way causing the unsuspecting asker to place their confidence in an incorrect solution. That frequently then results is the asker spending an inordinate amount of time trying to determine why they can't get their "this-is-clearly-the-correct-solution" answer to work -- only to discover later on that it was incorrect all the time!

"I'm no expert" (hehe), but I suspect that a common attitude (among experts) is:

"I'm not really all that knowledgeable in this area but hey, I'll take a stab at it and who knows, maybe I'll nab some points.  It will only take me a few seconds and what have I got to lose?"

And they are right -- at least for them -- because they really don't have much to lose. The problem though, is that the asker has got a lot to lose:  it's called time -- case in point is the wasted hour or two I spent yesterday going through several "expert" responses to essentially this same question, only to finally discover that none of them were correct.  And then, as if adding salt to the wound, I discovered that the correct answer could be given in a 3-4 word sentence and is a simple = (equal sign)!  Hold on please while I say it again:  AAARRRGGGHHH!!!      Thank you.

BTW, I don't think someone would give an incorrect answer purposely but instead, I suspect it's just a case of not thinking something all the way through.  You know, the fact is that it really wouldn't be all that big of a problem for an I'll-take-a-stab-at-it Expert to post a guess-type solution -- as long as they would just say that's what they are doing!  

Hey, maybe EE should penalize Experts who:

    1) respond to a question with an incorrect solution
        AND
    2) do it without including a disclaimer!  

It would be like one of those standardized exams we all hated so much in which there was a penalty for guessing.  LOL  Maybe EE could give them five buttons with a requirement to select one each time they post a response.  The buttons could range from:  

1 = "I'm clueless about this but I'd like to at least get 20-30 points somehow before the end of the month."

up to

5 = "I wrote the software you are discussing so don't argue with me."  LOL!

That is obviously a way-out-there idea but wouldn't it be nice to have something like that in place!

BTW, I have set my profile in another help forum to always include the following with any of my posts:

"A person who has never made a mistake is a person who has never tried anything new." ~Albert Einstein
"Any fool can make things larger and more complex.  It takes a touch of genius...to move in the opposite direction." ~Albert Einstein

Interestingly, both of those great quotes seem to be applicable just now.  Hmm...

Thanks again!
Steve,

Thanks for writing.  I was fairly fried when I responded and should have read your post more carefully.  It was clear to me after I realized what you were trying to accomplish was beyond just filtering Column B. Once I realized it was my misunderstanding,  I just missed the bell by a couple of minutes with the correct answer.

Glad you got what you ultimately needed.