Solved

Show rows with empty cells in Excel Advanced Filtering

Posted on 2011-02-23
6
392 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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