Solved

Designate empty cells when filtering in Excel

Posted on 2011-02-21
23
286 Views
Last Modified: 2012-05-11
Hello,

When using advanced filtering in Excel (2007), what entry in the Criteria area will result in displaying all rows which have empty cells in the corresponding column?

I have tried:    =""    and    <>*    and neither produce the correct result.

Thanks
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 3
  • +1
23 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34948833
use:
="="""""
or,
 <>"="""""

http://www.mrexcel.com/archive/Data/3253.html
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34948836
Here's a good reference for tips on advanced filters...http://www.contextures.com/xladvfilter02.html

if C1 is blank and C2 has the formula ="" it should be true.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34948845
or if C1 is blank, isblank(c1) will return true as well - a bit easier than counting quotation marks :)

Dave
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34948849
Highlight the entire range and then you will get the blank option :)

Please see snapshot.

Sid
Untitled.jpg
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34948918
The =C2="" works.  Don't put a field name on the criteria (per the contextures link I sent).

Here's the starting point:
 adv1
Here's the filter setup:
adv2
Here's the result after running the advanced filter:
adv3
See attached:

Enjoy!

Dave
AdvancedFilter-demo-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34948924
so rather than ="", you need to reference the first row of the column you are filtering, and the filter will apply to the entire column.

Thus =C2="" is sufficient to set the critiera for blank cells in the C column.

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34948935
Final comment -

=ISBLANK(C2) will work just as well as =C2=""

Dave
0
 

Author Comment

by:Steve_Brady
ID: 34966397
I appreciate the responses but for some reason your solutions are not reaching their destination (i.e. none of them work in my spreadsheet).  I have a hunch that the glitch is located somewhere in my gray matter.  :p  Therefore, to make it easier on my brain and less taxing for you, here is my question in pictures.

Suppose you have the table in Fig. 1
 1
and you want to filter for enteries = Red in the first column (Fig. 2).
 2
Fig. 3 shows that in your Advanced Filter box, the List Range is defined as B5:I16 and the Criteria Range is defined as B1:I2:
 3
And sure enough, filtering results in only those rows with entries = Red in Col B remaining (Fig. 4).
 4
Now, suppose you want to filter the same table for rows in which the Col B entry = Red and the Col G cell is empty or blank (Fig. 5).
 5
Inspection before filtering reveals that only rows 6, 10, and 14 match these criteria (Fig. 6).
 6
My question is:  What formula or value should be entered in Cell G2 so that filtering -- with the same List Range and Criteria Range as before (Fig. 7)
 7
gives the desired result as shown in Fig. 8?
 8
Thanks
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966415
But you original question was not that?

You wanted to filter blank cells and I gave you the solution with a snapshot :) ?

Sid
0
 

Author Comment

by:Steve_Brady
ID: 34966522
>>But you original question was not that?

You are correct.  My original question was this:

"When using advanced filtering in Excel (2007), what entry in the Criteria area will result in displaying all rows which have empty cells in the corresponding column?"

so just eliminate column B in the above example and then it will be exactly the same.

0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34973818
Steve - thanks for the "demo" data structure.  It helped me help you.

Here's your solution (see tip here: http://www.techrepublic.com/blog/msoffice/how-to-find-blank-records-using-excels-advanced-filter/3644)

See image, below - with original dataset, then advanced filter and results below it.
pictureAttached, is a spreadsheet like your demo data set which I typed in.  It is filtered to show blank cells in column g.

All you need is the "=" sign.

Dave
DesignateEmpty-r1.xlsx
0
 

Author Comment

by:Steve_Brady
ID: 34973880
Gentlemen, as a final comment before I attempt, somehow, to close this thread and attempt, somehow, to expunge it from my memory, please read through the thread found here.  Thank you.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34973913
That was hilarious.  Did you see my last post just above?  All you need is the = sign?

Dave
0
 

Author Comment

by:Steve_Brady
ID: 34973940
dlmille:  All you need is the "=" sign.

Dave, thanks for the post.  Needless to say, I posted my previous comment prior to seeing yours so now I will at least know how to assign points.

:)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34973943
Oh - and by the way, my first response was correct as wel - with the dataset I provided the response was correct.  

Steve - when you provide a dataset it clearly helps the E-E expert in providing a more timeline and appropriate solution.

Dave
0
 

Author Comment

by:Steve_Brady
ID: 34973966
>>That was hilarious.

...and I just posted that before seeing that!
0
 

Author Comment

by:Steve_Brady
ID: 34973985
>>Oh - and by the way...

...and again, I just posted that before seeing that!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34974006
:)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34974110
@Steve - sorry it took so long for me to get back to this thread.  I was in bed the last 48 hours with the flu.

I suggest never assigning points just to assign them.  You can request attention and ask for a question to be deleted if you believe the answers are unsupportive or you solved the problem through your own means.

Dave
0
 

Author Comment

by:Steve_Brady
ID: 34974155
>>dlmille:
=ISBLANK(C2) will work just as well as =C2=""

Dave, now I'm curious to know the function of the above solution because 1) I'd like to figure out how we got different meanings from my question, 2) I've never used =ISBLANK(), and 3) I want to learn as much as possible about filtering.

Thanks
0
 

Author Comment

by:Steve_Brady
ID: 34974166
>>never assigning points just to assign them

Thanks.  That's good to know.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34974230
Your original question:  When using advanced filtering in Excel (2007), what entry in the Criteria area will result in displaying all rows which have empty cells in the corresponding column?

BOTH my responses were based on the same question, but the second response was based on your data layout which conveyed much more meaning into your problem. And I could test with YOUR sample data to see if it worked.  I generally don't respond to questions unless I can prove my solution is correct.

BOTH my responses I also found alternative sources of documentation supporting my solution, and I provided those links - so these were not GUESSES at solutions.  However, sometimes we have to GUESS at what your data looks like. And SOMETIMES, its the data layout that makes all the difference!

My response - there are at least 2 ways to show rows with empty cells in a column.  

My first post showed that and =c2="" and =isblank(c2) would do the same.  I setup a sample dataset and demonstrated the veracity of that.

Today's post  is with column headers and multiple criteria - based on your submitted dataset, and it is correct as well, using = as the criteria.

Both work, and I provided datasets to prove it.

Cheers,

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34974274
I guess I am late for the party...

>>so just eliminate column B in the above example and then it will be exactly the same.

@Steve: I see what you mean. I was using the normal filter as shown in my snapshot and you actually wanted "Advanced Filter". Sorry I misunderstood your question.

Sid
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
autofill formulas using macro 8 53
Issue with nested if statement and vlookup formula 4 45
Embed Excel WS into PPTX 4 26
need count all combination 31 32
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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