Need help with Filtering process in Excel.

Posted on 2011-02-20
Medium Priority
Last Modified: 2012-05-11

I'm having trouble with the filtering process in Excel (2007).  I've got a spreadsheet with a variety of data in most of the cells in the range A1:BK2380.  However, I only want to filter rows 65-1400 based on criteria in column I.

As shown in the following screenshot (Fig. 1), column I has a heading in row 1 and rows 2-4 are the necessary three rows for filtering criteria mentioned in Excel Help.  Also, I've entered the filtering criteria ="=Met *" in cell I1.
In the Advanced Filter I entered the information shown in Fig. 2.
However, when I click OK, nothing happens.  I know there are multiple entries in the list range which satisfy the filter criteria so I am obviously doing something wrong.

Any help would be much appreciated.

Question by:Steve_Brady
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
  • 8
  • 7
LVL 50
ID: 34939033
Hello Steve,

does your list actually start in row 65 or above that row?

In the List range box you need to enter the full list reference. If you only want to filter a subset of rows, you could add a column to the table that has the row number (using =Row()), and then add criteria for that column, i.e. =">=66" and ="<=1440"

cheers, teylyn

Author Comment

ID: 34939220
Thanks for the response teylyn.

Yes, I have data above and below the list range I'm using.  

>>In the List range box you need to enter the full list reference.

So are you saying that I need to define the range to include all column I entries?  Does that include rows below the "last populated cell" (I just learned that jargon recently -- thank you very much! :) in column I if those rows have cells populated in other columns?  I can do either of those without a problem but just asking for clarification.  

Also, do I include the criteria rows in the list range or start the list range just below it?

I tried the latter as shown here (Fig. 3):
However, although I could tell it was running thru something, there was still no apparent filtering.  FYI, I did notice this in the bottom left border of the Excel window (Fig. 4):
LVL 50
ID: 34939385

please see the attached file for illustration. I have a data set of dummy names and addresses.

Above the data, there are the cells for the filter criteria. Filter for all females by entering the word "female" (without the quotes) into B2.

Then click a cell in the data table and click Data > Advanced. The dialog box will open and you'll see the marching ants around the whole table:


The criteria range is $A$1:$J$2. Hit Ok and you'll see all females in the list.  So far, so good.

Now, if you only want to see the females between rows 20 and 80, then you need to add two criteria to the criteria range. In cell I2 enter


In cell J2 enter


Click a cell in the table again and open the Advanced Filter dialog. Hit OK and you will see only females from rows 20 to 80. I've marked these in red, so you should not see any black rows in the filter result.

There is an excellent tutorial for Advanced filter at http://www.contextures.com/xladvfilter01.html.

Hope that makes it clearer.

cheers, teylyn
Technology Partners: 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!


Author Comment

ID: 34939709
Thanks teylyn, that's super helpful!

Some questions:

Does it always default the criteria range to the full table width?  Even if only one column has a criterion entry as in my initial question?

Is clicking somewhere in the table -- and doing it before opening the Advanced box (AdvBx) -- a neccesity?

What is the purpose of the AdvBx if you just open it to click OK?  I presume it is so the List and Criteria ranges can be modified manually.  Is that correct and are their limitations?

How did the AdvBx find the second Row column (J)?

What about discontinuities in criteria and/or data?  I added a column of =RANDBETWEEN(100,999) in column N (N9:N132) with the heading "Num."  I also entered the value 498 in cell N3.  In different tests, I got the following:

    With no headings in col K1:M1 or K8:M8  >>>  the  Adv box did not include Col N
    With headings in K1:M1 but not K8:M8  >>>  the  Adv box did not include Col N
    With headings in K1:M1 & K8:M8  >>>  the  Adv box included Col N in the List but not Criteria range (see Fig. 5):
I did not test with discontinuities in data.

BTW, I have always been a bit confused by the filter function so I haven't used it much but thanks to your sample file and being able to ask a few questions (like above), I think I might be able to start including it in my bag of tricks.  I can definately see how it can be a big time saver compared to sorting all the time.

Thanks teylyn!

Author Comment

ID: 34939745
It's still not working.  I've got losts of empty cells randomly occurring throughout the table.  Is that the problem?
LVL 50
ID: 34939940

>> Does it always default the criteria range to the full table width?  Even if only one column has a criterion entry as in my initial question?

The criteria range can be just one column but it must be at least two rows. The field name in the first row and the criterion in the second row. See steps 2 and 4 in the tutorial I linked to above.

>> Is clicking somewhere in the table -- and doing it before opening the Advanced box (AdvBx) -- a neccesity?

No, but if you do, the table range will be automatically entered into the List Range box. Click somewhere outside of the table and open the Advanced Filter to find that the List Range box is empty and you have to manually select the table you want to filter. Easier to just click a cell in the table and let Excel identify the dimensions of the table automatically.

>> What is the purpose of the AdvBx if you just open it to click OK?  I presume it is so the List and Criteria ranges can be modified manually.  Is that correct and are their limitations?

Yes, it's used to make changes to the settings, most often to the criteria range. I prefer to have the criteria range as wide as all the columns, even if I don't enter any criteria in some columns. I then just hit the button and enter and don't need to worry about the criteria selection.

>> How did the AdvBx find the second Row column (J)?

I selected the criteria range manually and Excel remembers the last selection (most of the time. Sometimes it doesn't. Haven't found a pattern or explanation)

>> What about discontinuities in criteria and/or data? ...

With the active cell somewhere in the data table, Excel will try to identify a contiguous table of data, up/down to the next blank row and left/right to the next blank column. The idea is that your data list to be filtered is a table with headers, no blank columns, no blank rows. If you click a cell in such a list and open the Advanced filter dialog, this range will be in the List range box automatically.

There is no automatic suggestion for the filter criteria when you open the dialog for the first time. The filter criteria do not have to reside above the table, either. They can be anywhere in the sheet, as long as the first row contains the same column header as the list and the range includes at least one row for the criteria.  You can have a list with data from A10:M100  and have your criteria range in P1:P2 (for just one criterion) or O2:Q5 for criteria on up to three fields and AND/OR combinations that are written in several rows of filter criteria.

It's often easiest, though, to keep the criteria range above the actual data table because:

- if the data table starts in row 1 it's hard to find a range for the filter criteria that is easily accessible and does not get hidden when the filter hides rows.
- it's easy to just copy and paste the table headers to avoid typos in the criteria range field names
- it's easy to set up the filter criteria above the columns where the data resides, so there is a visual connect between filter criterion and data source.

Hope that helps you in your explorations. I find the contextures tutorial very helpful, especially since the Excel Help for 2010 does not find anything useful when searching for "Advanced Filter"

cheers, teylyn


Author Comment

ID: 34939942
Still not working.  However, I think it's finding every row for some reason because I tried switching the AdvBx to Copy to another location
and it copied the entire table to the specified destination.  So it seems to be going thru the process but why would it find or match to every row even though only a single filter criterion is present in the criteria range?

Also, can you comment on the wildcard syntax?  The Excel help says to use this:


However, when I enter this:


directly into the cell, it filters?

Author Comment

ID: 34939969
>> I find the contextures tutorial very helpful

OK, I'll spend some time there.  Thanks
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 34939971
I think someone needs to write a book about this. Only, who'd buy it???

For the criteria range use only the rows where you have entered criteria. If you don't have any entries in rows 3 and 4, let the range refer to $A$1:$T$2 only.

If you use wildcards, you don't need the = sign.

Ice* will find

Ice cream

If you only want Ice, then use


and it will not find the cream and the berg, but just the Ice.

Make sense? Again: Contextures has good explanations for the wildcards.

cheers, teylyn

Author Comment

ID: 34940333
>>For the criteria range use only the rows where you have entered criteria. If you don't have any entries in rows 3 and 4, let the range refer to $A$1:$T$2 only.

OK, got it!  ARRRGGGHHH!!!  I knew it had to be something simple.  

For some reason, I was thinking that the Criteria range has to include all the rows above the List range.  The Excel Help said something about having three blank rows.  So I was selecting rows 1-4 even tho I only had criteria in one row.  When I read (and followed) your sentence above, it worked!  Also, I just tried removing all but two rows (heading and one criteria) and it works fine -- so ARGH! again.

Thanks again teylyn -- another happy EE'er!
LVL 50
ID: 34940380
Steve, as so often, it's been a pleasure to explore a subject with you. You have a knack of putting your finger on the sore spots, and more often than not, I feel I'm just a hair's width ahead of you. Or I know where to look up what I don't know.

Have you ever thought about writing an EE article about the findings of your explorations? I think there are a lot of users out there who would welcome some down-to-earth user insights that don't require a degree in VBA or advanced, over-complicated formulas.

cheers, teylyn

Author Comment

ID: 34940579
LOL  You are kind teylyn.  Thank you for the nice words.

I would very much enjoy doing that but unfortunately, I have got some things breathing down my neck right now which demand my focus and time.  Maybe I could do something like that later on but I'm not quite sure what you are referring to by "some down-to-earth user insights."

If there is something I know or that I'm doing which would benefit other users then I'd be happy to write about it.  However, unlike experts who read both user and expert comments in threads, I have to confess that I generally don't spend any time in threads other than my own.  Thus I am not really familiar with what other users ask or how they ask it.

One more quick question on the filters please.  It appears that if multiple criteria are used, the filter treats them as a string of =OR() arguments (i.e. it matches any rows that satisfy any of the criteria).  Is there a way to filter with criteria treated as =AND() arguments where all of them must be satisfied?

Thanks again for the help, teylyn.  I have to tell you that your name is among a few (a small few) experts whose names elicit a smile/sigh of relief on my part when I see they have responded to one of my questions.  Two more are Barry (Houdini) and Brad (byundt) and a maybe a couple of others whose names I don't recall now.  But you are always clear in your responses and you stick with it  -- even when the party on this end is having gray matter issues:  present case in point!  That effort does not go unnoticed.

Have a great week!
LVL 50
ID: 34940931
<smile>Ta very much. Re an article: I was thinking along the lines of "Exploring Excel XXX feature starting from scratch". Then just write down what you find out along your journey.


See here: http://www.contextures.com/xladvfilter01.html#CriteriaRange

Basically, if you enter all criteria in the same row, then it will be treated as "AND".

color  size  value         = color = blue AND size = 20 AND value >80
blue     20   >80

If you enter each criterion in a different row, the criteria will be treated as "OR"

color  size  value         = color = blue OR size = 20 OR value >80

There are a multitude of possibilities combining these.

In the example I attached earlier, you will notice that the criteria range includes two field headers for row, one showing >=20 and the other showing <=80 in the respective criteria field. Since they are on the same row, they will be treated as "AND" (filter all records that have a Row value >=20 AND a Row value <=80). So, if you want to enter multiple criteria for one field, you need to list that field more than once in the criteria range. With all criteria in one row, it's an "AND" condition.

cheers, teylyn
LVL 85

Expert Comment

by:Rory Archibald
ID: 34941756
Just as an FYI, you don't need wildcards if the data starts with your criteria - you can just use 'Ice' and it will find anything starting with 'Ice'. More often annoying than useful, IMO. :)

Author Comment

ID: 34946554

Just as an FYI, you don't need wildcards if the data starts with your criteria...

Thanks rorya.  That's good to know.

BTW, does filtering allow you to get more creative?  Like is there a way to filter by a certain font or fill color, etc?
LVL 50
ID: 34946641
Not with Advanced Filter, as far as I know. The Autofilter has some options to sort/filter by font or background color in 2010. Not sure if 2007 has it, too.

cheers, teylyn

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 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