Advanced Filter in Excel (Table of Contents)
Advanced Filter in Excel
Advanced Filter in excel is next level filter option available in the Data menu tab under Sort & filter section which is used to filter the selected data as per the criteria we set for this. For this, first, we need to scrub the data by removing the blank cell, keeping header to all the columns. To advanced filter, we need to define criteria by which we need to filter the data and the criteria should be placed in separated cells from the table. Once we select the Advanced Filter Option, there select the complete range which we want to filter then select the cell where we defined the criteria.
What is the difference between regular and advanced filter?
- You can use the advanced filter for more complex criteria filtering. (I will explain in detail with an example.)
- A regular filter will filter data on existing dataset while with the latter you can extract data to some other location keeping original data intact.
- The advanced filter can be used to extract unique entries in our dataset.
- Regular Filter is sequential filter viz. you can’t use multiple criteria’s simultaneously to extract records from one dataset, you have to extract data satisfying each criterion which ultimately leads to duplicity.
E.g. If you want to extract records where Reporting Manager is “Aakash Harit” or Employee name is “Vishal Kumar”, you have to extract data 2 times using regular filter, first data having Aakash Harit is RM and second, when Emp name is Vishal, while using advanced filter you can extract all unique records in one go.
How to Use Advanced Filter in Excel?
This Advanced Filter is very simple easy to use. Let us now see how to use the Advanced Filter in Excel with the help of some examples.
Advanced Filter in Excel Example #1
- Suppose you have the following dataset, and you have to get all the records where order total is greater than 900 and employee name is, “Nishu Kumari”.
- To use an advanced filter with some criteria, first, you have to copy all the headers & paste them somewhere else in the worksheet. (In my case, I am going to use blank rows to make it easier to understand.
- Now, specify the conditions under these headers according to your requirement, which will act as an input in a filter.
- Now, select the entire data set along with the headers and Go To Data tab – Sort & Filter. This will open the Advanced Filter dialog box.
- Hotkey to apply the advanced filter after selecting dataset is (Alt key+ A+ Q).
- Now, as shown above, you have to enter the following details:
- Action: It has 2 options. First, filter the list, in-place (this will remove the original data and result of this filter will be placed on the same location) and Second, to copy another location. (this will allow you to save filtered data on location)
- List Range: It refers to the dataset from where you want to find data. (Here it’s A7: K35)
- Criteria Range: In this Criteria is mentioned, (A1: K2)
- Copy To: This cell will be activated if you go with the second option in Action criteria.
- Copy Unique Records Only: Check this only if you require unique records only.
- I have entered all the details in this dialogue box and chose to copy to another location(A38) with unique records.
- It will look somewhat as in Screenshot on next page. Now click ok, and it will give all unique records.
The output is given below where it shows only those data which contains the Employee name as Nishu Kumari.
Filtered data with complex criteria as constructed above.
Advanced Filter in Excel Example #2
Now, there are many combinations of criteria which can be used in Advanced Filter. (Using AND and OR Criteria) Some examples are:
- Filter data where Customer Name is “Chloe Jones” AND Product name is “Apple”.
I have entered all the details in this dialogue box and choose to copy to another location(N3) with unique records.
The output is given below:
- Filter data where the Reporting Manager is “Aakash Harit” AND Product Qty is greater than 50.
I have entered all the details in this dialogue box and choose to copy to another location(N9) with unique records.
The output is given below:
- Filter data where Employee name is “Rajkumar Singh” AND Product Name is “Pineapple” AND order total is greater than 100.
I have entered all the details in this dialogue box and choose to copy to another location(N17) with unique records.
The output is given below:
This above table shows empty data because it does not matches the given condition.
- Filter data where Employee Name is “Vishal Kumar” OR Order Total is less than 500.
I have entered all the details in this dialogue box and choose to copy to another location(N22) with unique records.
The output is given below:
- Filter data where Employee Name is “Abhay Gaurav” OR “ Nishu Kumari”.
I have entered all the details in this dialogue box and choose to copy to another location(N38) with unique records.
The output is given below:
- Filter data where the Reporting Manager is “Divya Sharma” OR Product Name is “Grapes”.
I have entered all the details in this dialogue box and choose to copy to another location(N50) with unique records.
The output is given below:
From above examples you might have noticed that while using AND criteria, all values must be mentioned in the same row and in OR criteria, all values must be in different rows.
Advanced Filter in Excel Example #3
Another important feature in Advanced Filtering is filtering data using Wild Card Characters. There are wildcard characters:
- An asterisk (*): It represents any number of characters. E.g. To filter data with Employee name starting from “R”. You will write “R*”, So any employee name starting with initials R will be filtered out.
I have entered all the details in this dialogue box and choose to copy to another location(N9) with unique records.
The output is given below:
- Question Mark (?): It represents one single character. E.g. filter data with where employee name initials are “Nish”. Filter criteria will be like, “Nish?” and it can mean Nishu or Nisha.
I have entered all the details in this dialogue box and choose to copy to another location(N9) with unique records.
The output is given below:
- Tide (~): It is used to find any wildcard character within a text.
Advanced Filter in Excel Example #4
You can also remove duplicates or you can say quickly extracting the unique records from the data set.
- Now suppose you have the following data set which has some duplicate entries(highlighted) and you want to extract unique records from a dataset, you can use the advanced filter option to do it, keep in mind that there is no need to mention any values in criteria option and simply check unique records option. This will give you records with unique values.
- After applying the filter, it will give a result, as shown below:
- You can notice in below image; all duplicates are removed. Last 3 rows are empty
- However, latest versions of Excel (mainly after Excel 2007), we have an option to remove duplicates from a dataset (Go to Data Tab.Remove Duplicates or use hotkey Alt Key+ A+ M) but it will alter the original dataset. But if you want to keep original data intact, you can use advanced filter to get a dataset with unique entries at some other location.
Key Notes:
- While applying an advanced filter, always take care that headers in the criteria should be exactly the same as in data set. Viz. there should be no spelling errors, space errors or even case errors, although alignment can be mixed. E.g. in above you can mention criteria like Product name first and employee name second. But headers should be exactly the same, as shown below.
- You can’t UNDO advanced filtering when copied to some other location.
- If you are using advanced filter to extract unique entries, make sure to select headers also, the otherwise first entry will be taken as headers in it.
Things to Remember
- Advanced Filter is an advanced version of regular filter used to filter data with complex criteria’s and multiple conditions.
- It is used to extract unique entries from the dataset.
- You must include headers while selecting data set and the headers in criteria must be exactly in the same order as in dataset.
- You can’t undo it if filtered data is copied to another location.
- Shortcut to apply an advanced filter after selecting dataset is (Alt key+ A+ Q).
Recommended Articles
This has been a guide on Advanced Filter in excel. Here we discuss how to Advanced Filter in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
