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:-
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:-
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:-
There are several options. See below in detail:-
(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 is the box, where we will drag our whole dataset 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.
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.
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:-
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.
Now press “OK” and see that our unique filtered data has been relocating to a different place where we have given the cell reference.
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.
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.
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:-
“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:-
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”).
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.
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 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”
Below given example of containing the name of “Chai” or “Chang”
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 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:-
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:-
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:-
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.