Excel Advanced Filter – How to set criteria and use

What is Advanced Filter?

Advanced Filter is a next level filtering method after Auto Filter. In our previous tutorial about AutoFilter, we have learned that “Auto Filter” can filter the data: – by text, by cell color, by font color etc. Auto Filter has some limitation like it works with fix dataset and cannot relocate to a new location. But in Advanced Filter, we can easily relocate our filter dataset to a new location or a new worksheet also. We can put multiple criteria in Advanced Filter.

Advanced Filter vs. AutoFilter                                  Set up the Database for Advanced Filter
Set up the Criteria for Advanced Filter                 Remove Duplicate and Find Unique list
Relocate Filtered Data into Another Sheet          Using AND Criteria
Using OR Criteria                                                          Using AND with OR criteria
Using Wildcard Characters

Advanced Filter vs. AutoFilter

Auto Filter works within a fixed data-set and can filter by cell color, by font color, by cell value and so on. “Advanced Filter” works with data only, not like cell color or font color etc.

Auto Filter cannot relocate data on the worksheet but in Advanced Filter can move our filtered data into a new location or worksheet also.

Excel “Auto Filter” has all the built-in filter option but in “Advanced Filter”, we have to set criteria.

Excel Auto Filter allows up to 2 criteria for filter whereas Advanced Filter can set many criteria.

Set up the Database for Advanced Filter

For setting up the database, the very first row of the database should have unique headings of each column with the bold title, so we can easily recognize it as headings of the database. Under each columns headings the data should be related to heading title, like dates under date heading column, numeric value under related heading column and so on. Make sure there are no blank rows in the database if there are any blank rows appearing, remove with 5 Easy Methods To Get Rid of Blank Rows. Blank rows are not allowed in data-set, blank rows would be after the last row or column.

 See image below:-

 

Advanced Filter

 

Set up the Criteria for Advanced Filter

For setting up the criteria, copy all the headings of the database in a new location and put the criteria under each heading.  See image below:-

Advanced Filter Criteria

Here in this image, we have set criteria for the date”>3/12/2013”, Category is “Beverages” and Value must be “>500”.

Where to Set the Criteria

For setting up the criteria in Advanced Filter, Go Data tab –> Sort & Filter –> Click on “Advanced See image:-

Click on “Advanced” button. See image:-

Advance Filter Criteria Box

There are several options. See below in detail:-

Action

     (a) Filter the list, in-place

By clicking on this radio button, it will filter the data in-place only.

     (b) Copy to another location

Click on the radio button, we can move our filter data-set into another location on the same or a new worksheet also. Give a cell reference or a cell address in a “Copy to” filed box

List range

List range is the box,  where we will drag our whole dataset range.

Criteria range

For criteria range, we can say that “what do we want to filter?“, we can see the example in below image. In the below image selected area is criteria range for the database given below. Kindly note that criteria range has the same heading as our database has.

 

Advanced Filter Criteria Range

 

Copy to

In this box, cell reference where we want to locate our filtered data. Whether on the same worksheet or another one.

Unique records only

At the end of the criteria box there are a checkbox “Unique records only”, this option helps us to remove the duplicate records and filter unique records only. This is optional in other cases.

Remove Duplicate and Find Unique list

In Excel 2007 onwards, Excel introduces “Remove Duplicate” feature. Applying this method, it will remove the duplicate records permanently. If we want to keep the original dataset as intact, then we have to copy it in other places, in the worksheet. But Advanced Filter gives us a freedom to copy unique filtered data into another place and keep original dataset in the original form.

Below given dataset image, there are some duplicate records in it. With the help of “Advanced Filter,” we will remove these duplicate data from the original dataset. So there are two methods to remove duplicate data from the original dataset.

  • Apply the Advanced Filter on the same dataset.
  • Keep the original dataset intact and filter unique data in a different location.

DataSet

 

Apply the Advanced Filter on the same dataset

Go to Data tab –> Sort & Filter –> Click “Advanced”

Where Advanced Filter box is appearing with the dataset.

 

Drag the whole dataset in the “List Range” from “A5: D20” and leave “Criteria range” and  “Copy to” box blank. “Unique records only” make it as a tick mark and press “OK”. We will have the unique filtered list and all duplicate records have been removed.

See image below:-

Advanced Filter on the dataset

To remove duplicate, make sure all the dataset should be selected in the “List range” area.

Keep the original dataset intact and filter unique data in a different location

Go to Data tab –> Sort & Filter –> Click “Advanced”

The “Advanced Filter” box will appear. Now we want to keep our original dataset as in original form and filter the unique records in a different location. So for that, we have to give a cell reference or a cell address in “Copy to” area and hit “OK”, our unique filtered data will relocate into this place.

 

Advanced Filter in New Location

Now press “OK” and see that our unique filtered data has been relocating to a different place where we have given the cell reference.

Advanced Filter in New Location

Relocate Filtered Data into Another Sheet

Now we will work on a new topic which is “Filter Unique Records into another Sheet”, is it possible? Let’s check it.

Filter Records in Another Sheet

When applying for “Advanced Filter”, we are working in the “Sheet4” and given a cell reference in the “Copy to” box as “Sheet5! $A$1” and tick on “Unique records only” and press “OK” see what happens.

 

Error for new location

 

An error message “You can only copy filtered data to the active sheet.” So by this way, we cannot relocate our data to next sheet but why? The error message is saying “You can only copy filtered data to the active sheet”, it means if we will make our next or “Sheet5” as an active sheet, then is it possible.

So first make “Sheet5” as active where we want to relocate our data and then apply Advanced Filter from that worksheet, then it is possible to relocate our data into a new worksheet.

Activate “Sheet5” and apply “Advanced Filter” from it. See the image below:-

Copy Data into New Sheet

“Sheet5” is active now, we have given the cell reference “Copy to” as “Sheet5! $A$1” and “List range” as given in the box from “Sheet4”, and mark tick on “Unique records only” and press “OK” and we will have our unique records into our new sheet.

Advanced Filter Using AND Criteria

If we are putting more than one criteria in the same row, it as a “AND” criteria. 

See Example below:-

 

Advanced Filter AND Criteria

 

In this example, there are more than one criteria, first date criteria should be “<=3/12/2013” and criteria for “Category” is “(=”=Beverages”)” and value must be >=500.

Whenever put a text in the criteria field, always use below-given format or method to write text: = [=”=Text”] (as in above example: – =”=Beverages”).

Advanced Filter AND Criteria

Always select the database with headings otherwise it would consider the first row as the heading of the dataset.

Leave at least one row blank between dataset and criteria area.

Another example of AND criteria:-

A date should be “<=3/12/2013”, “Product Name” should be (=”=Chai”) and Value should be “>=500.

Advanced Filter AND Criteria

In this below-given criteria range date should be “>=12/5/2013” and value should be “>=500” and see the result below the criteria range.

Advanced Filter AND Criteria

 

Advanced Filter Using OR Criteria

When we are putting the criteria in the different rows, is called “OR” criteria.

In the below example, Category must be “Seafood” or “Beverages”

Advanced Filter OR Criteria

Below given example of containing the name of “Chai” or “Chang”

Advanced Filter OR Criteria

Advanced Filter Using AND with OR criteria

In the below-given example, the filtered data is for ‘Category’ must be “Seafood” or “Beverages” and ‘Value’ must be “>=1500” or “<=700”.

The format of “AND” and “OR” is as follows:-

(Category = Seafood AND Value = >=1500) OR (Category=Beverages AND Value=<=700)

Advanced Filter AND and OR Criteria

 

Advanced Filter Using Wildcard Characters

Advanced Filter allows us to find our data with the help of wildcard characters. There is three wildcard character we use to find the data accurately.

The * Wildcard (asterisk)

The ? Wildcard (question mark)

The ~ Wildcard (tilde)

The * Wildcard

This wildcard character will search for all the related word containing the same initial letters before the (*) asterisk sign.

See example below:-

Advanced Filter asterisk wild card

The? Wildcard

This wildcard characters called “question mark” (?) and this is used to find out any single characters of any word, the characters we do not know we can use to find out that characters, we can use multiple “?” to find out one character for each. See example below:-

Advanced Filter ? mark wildcard

 

The ~ Wildcard

This wildcard character is called “tilde” (~) and if any word containing any kind of wildcard characters to find out that particular word, we can use this wildcard characters to find out that word.

See image below:-

Advanced Filter ~ mark wildcard

 

Things to Remember

 

Never select any blank row with criteria, It may cause the wrong result.

For setting up the criteria, make sure all the headings should be same as in the dataset.

After Filtered data in the new location cannot be undone.

 

Thanks

Narendra (Excelnsg.com)

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.