What Is Advanced Filters In Excel?
Advanced filter in Excel allows the user to filter the data based on two or more criteria like filtering exact matches, and partial matches with the help of wildcards, filtering rows that match items in the item list, and many other filtering conditions.
With the regular auto filter option, we can only filter the data. Still, with the advanced filter, we can not only filter the data but also copy the filtered data to different cells or worksheets as well.
For instance, if we have sales data, we can filter out one product where the quantity sold is >200 units. In case there are duplicates, instead of finding duplicates, we can also filter out unique records by using the advanced filter option in Excel.
Table of contents
Key Takeaways
- The advanced filter is used to filter the data based on the given criteria in the cells.
- We can filter the data in the same place and copy the filtered data to the new location.
- We can use wildcard characters in the criteria field to partially match the data.
- The advanced filter supports logical formulas that return TRUE or FALSE. AND & OR are the most used formulas in the advanced filter.
- When copying the data to the new location, it will copy the formatting of the actual data table.
How To Use Advanced Filter In Excel?
Using advanced filter in Excel is not the same as other auto filters. First, we need to organize the data in our worksheet. For instance, we have the following sales data in an Excel spreadsheet.
We need to filter the data for the customer ‘Claudia Miner’ where the units sold are >500. The steps to apply an advanced filter are listed as follows.
- The first thing we need to do is type our criteria or condition in cells. Always set up the criteria on the right side of the data table with a few blank columns.
In this example, we are trying to filter the data for the customer ‘Claudia Miner’ where the units sold are >500, so we set up the criteria filled as shown in the following image.
Note: The header of the criteria table should be the same as the actual data table. - Go to the Data tab, under Sort & Filter, click on Advanced.
This will bring the Advanced Filter window like the following.
Let us understand this window before we apply the further steps. We have two options under Action.
Filter the list, in place: This will filter the given criteria rows in the same table.
Copy to another location: This will filter the given criteria rows to the specified target cell. If we click on this option advanced filter will ask us to choose the target cell to copy the filtered data.
List Range: This is simply the data range that we trying to filter. In our example data range is from A1:C18.
Criteria Range: What criteria will be applied to filter the data in the list range. In our example criteria range is E2:F3.
Unique Records Only: If we check this option, it will filter only the unique records and no duplicates will be filtered out. - Select Copy to another location and choose the List range as A1:C18.
- Select the range E2:F3 for the criteria range.
- For the Copy to: option, choose any empty cells in the same worksheet. We have chosen cell J1.
- Ignore Unique records only option and click OK.
As shown in the above image, the criteria and satisfied rows have been filtered out. This is because all the blue colored rows in the original data set belong to customer ‘Claudia Miner’ but the units sold are less than the given criteria of 500, hence not filtered by the given criteria.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Example #1 – Criterias With Numbers
We can also apply the advanced filter to filter the data based on numerical logic. For example, we can use the following comparison operators along with numbers.
- Greater than or equal to (>=)
- Less than or equal to (<=)
- Equal to (=)
- Greater than (>)
- Less than (<)
- Not equal to (<>)
For instance, we have the following sales data in an Excel spreadsheet.
From the above table, we need to filter out fields where the quantity is >50, and the sales value is <4500.
- Step 1: Create a criteria table like the following.
- Step 2: Go to the Data tab, under Sort & Filter, and click on Advanced.
- Step 3: This will bring the Advanced Filter window. Choose Copy to another location and choose the listed range from A1:E12.
- Step 4: Choose the criteria to range from G2:H3.
- Step 5: Choose the Copy to: location as any empty cells.
- Step 6: Click on OK. We will have rows filtered based on the given criteria.
It has filtered out where the quantity is >50, and the sales value is <5000.
Example #2 – Criterias With Dates
We will continue with the same data from Example #1. This time we will use dates to filter out the data.
Assume we need to filter the data between 09th May to 28th May, and the sales value is <4000.
- Step 1: Create a criteria field like the following table.
We have entered two date criteria in cells G2 and H2, respectively. It will apply the criteria to filter the data from 10-05-2022 (>=) to 28-05-2022 (<=).
And, for sales value, we have entered the criteria as <4000 values.
- Step 2: Open the Advanced filter under the Data tab.
- Step 3: Click on the Copy to another location option.
- Step 4: Choose the list range from A1:E12.
- Step 5: Choose the criteria range: from G1:I2.
- Step 6: For Copy to:, choose the cell where we want to filter out the data.
- Step 7: Click on OK. It will filter the data based on the given criteria.
It has filtered out data for 09th May and 16th May (colored rows) where the sales value is <4000.
Note: While creating the criteria for dates, you need to mention the proper date format that Excel can read, like 01-05-2021 or 01-May-2021.
Example #3 – Criterias With Text
Continuing with the same data from the above two examples, we will look at how to use the criteria of text values.
For instance, if we want to filter the data only for the city ‘New York’ then create a criterion range like the one below image.
Step 1: Now open the advanced filter and choose the list range from A1:E12.
Step 2: Choose the criteria range as G1:G2.
Select the cell to copy the filtered values.
Step 3: Click on OK, and it will filter out the data only from the city ‘New York’.
Note: It will copy the same formatting as well. We can see the same in the above image.
Assume, we need to filter out the data for two cities like ‘New York’ and ‘Los Angeles’. In this case, your criteria range will be like the following image.
Step 4: Open the advanced filter and apply the necessary fields.
Step 5: Click on OK.
It has filtered the data only for cities ‘New York’ and ‘Los Angeles’.
Let’s do a simple tweak to the criteria here.
Assume we need to apply a sales value filter for these two cities where the sales value should be >4000 for New York and sales value should be <2000 for Los Angeles.
Step 6: Design the criteria range like the following one.
Step 7: Now apply the advanced filter, as shown in the following image.
Step 8: Click on OK.
So, filtered values are as per the criteria given.
Example #4 – Criterias With Wildcard
The advanced filter works with wildcard characters as well. Following are the Wildcard characters we can use in the advanced filter.
- Asterisk (*) = This is used to match any number of characters. For instance, “Ban*” could mean Bangalore, Bangladesh, Banana, and many other words starts with Ban.
- Question Mark (?) = This is used to match any single character in the word. For instance, “Sh?ll” could mean Shell, Shall.
- Tilde (~) = This is used to find any wildcard characters in the word. For instance, ?,*, and ~.
For instance, we have the following fruit sales data in an Excel spreadsheet.
Step 1: We will use the various wildcard characters to filter the data using an advanced filter. We have the following criteria set up in the criteria field.
*Banana* = We have used an asterisk (*) on either side of “Banana”. Here, the asterisk will match any number of characters on either side of the Banana.
Step 2: Open the advanced filter and apply the necessary fields like the following.
Step 3: Click on OK.
Step 4: The advanced filter has filtered all the products which have the word ‘Banana’.
Similarly, we have the following criteria set up.
Step 5: Apply the advanced filter as shown in the following image.
Click on OK, and we will have the following list filtered.
It has filtered all the product names starting with the word ‘B’.
In this way, by using wildcard characters we can filter partial matching data.
Example #5 – Criterias With Formulas
Continuing with the same data of Example #4, we have modified the data with the current month’s sales and the previous month’s sales.
We will use formulas in the advanced filter. For instance, we need to filter all the rows where the current month’s sales are more than the previous month’s.
So, to perform this, first set up criteria like the following.
Step 1: We have applied the simple logical formula to check whether the current month’s sales (C2) are greater than the previous month’s sales (D2). The very first value of the data condition is TRUE, so we can apply the advanced filter.
Step 2: Click on OK, and it will filter all the rows where the current month’s sales are more than the previous month’s sales.
Using AND And OR In Advanced Filters
The advanced filter in Excel works well with logical formulas AND & OR conditions. Taking the same data from example #5 into consideration, we will try to filter the data based on the following condition.
- AND Condition: Filter data where the current month’s sales are >300 and the previous month’s sales are >500.
Set up the criteria like the following.
Apply the advanced filter.
Click on OK. It will filter the data where the current month’s sales are >300 and the previous month’s sales are >500.
Similarly, if we want to filter the data with the following condition.
- OR Condition: Filter data for either month’s sales is>500.
To perform this, we need to set up the criteria field slightly differently here.
Now, apply the advanced filter.
Click on OK. It will filter the data where either of the month’s sales is >500.
Copy Filtered Data In Destination Sheet
We can copy the filtered data to the different worksheets as well. For instance, we have the following data in an Excel spreadsheet.
We need to filter the data where the quantity is >50 to a new worksheet called Filtered Sheet. First, create a sheet like this.
Come to the newly created worksheet and open the advanced filter.
Click on Copy to another location option.
In the list, place the cursor, and navigate to the data worksheet.
As we can see, we have selected the range of cells from A1:E12 in the worksheet named Filter Criteria.
For the criteria range, also choose the criteria in the same worksheet.
The Copy to option gives an excel cell reference of the destination sheet.
Click on OK. It will copy the filtered data to the destination sheet.
Important Things To Note
- The advanced filter requires the criteria as a cell reference,, and no manual entry of the criteria is not allowed.
- The criteria headers should be exactly the same as the headers of the actual data table.
- To copy the filtered data to the new worksheet, we have to apply the advanced filter from the new worksheet, not from the actual data worksheet.
- We cannot undo the action of the advanced filter in excel when we copy the data to another location.
- Click on the ‘Unique Records Only’ option only if we want to eliminate any duplicates in the data table.
Frequently Asked Questions (FAQs)
For instance, we have the following data in Excel.
Assume, we need to filter the data where the target value is >30000. Set up the criteria range like the following one.
Go to the Data tab and click on Advanced Filter in excel.
Choose Copy to another location option.
In the List range: option, choose the data range from A1:B13.
Choose the criteria to range from D1:D2.
Choose the Copy to: and select any of the empty cells.
Click on OK, and it will copy the data filtered data.
Only two months have a target of >30000.
If we choose Copy to another location, we cannot undo the action. However, if we choose the option of Filter the list in place, then we can remove the advanced filter like how we remove the normal filter.
Using the advanced filter, we can apply various conditions in cells, extract unique records, and, more importantly, we can copy the filtered data to a new worksheet or in the same worksheet at a different location.
Download Template
This article must be helpful to understand Advanced Filter in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Advanced Filter in Excel. Here we learn how to use advanced filters with various criteria, examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply