Solved

Show rows with empty cells in Excel Advanced Filtering

Posted on 2011-02-23
6
362 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
  • 4
6 Comments
 
LVL 1

Expert Comment

by:xllvr
ID: 34967094
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
 
LVL 1

Expert Comment

by:xllvr
ID: 34967148
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
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 34967164
Hello,

in the field for the criterion just enter

=

Nothing else.

cheers, teylyn
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:xllvr
ID: 34967179
Oops...just got there myself.  Teylyn is absolutely correct.  Just use = in the for that column's criterion.  Thanks, teylyn!
0
 

Author Comment

by:Steve_Brady
ID: 34973710
>>...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
 
LVL 1

Expert Comment

by:xllvr
ID: 34975487
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now