• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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
0
Steve_Brady
Asked:
Steve_Brady
  • 4
1 Solution
 
xllvrCommented:
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.
0
 
xllvrCommented:
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?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

in the field for the criterion just enter

=

Nothing else.

cheers, teylyn
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
xllvrCommented:
Oops...just got there myself.  Teylyn is absolutely correct.  Just use = in the for that column's criterion.  Thanks, teylyn!
0
 
Steve_BradyAuthor Commented:
>>...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!
0
 
xllvrCommented:
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now