Solved

Show rows with empty cells in Excel Advanced Filtering

Posted on 2011-02-23
6
375 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:
Ingeborg Hawighorst earned 500 total points
ID: 34967164
Hello,

in the field for the criterion just enter

=

Nothing else.

cheers, teylyn
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

867 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

19 Experts available now in Live!

Get 1:1 Help Now