Nehad Ulfat
In Microsoft Excel, using Advanced Filter with multiple criteria allows us to add different logics and functions to execute a wide range of extractions of data. In this article, you’ll get to learn how you can use this Advanced Filter properly with multiple criteria in Excel.
The above screenshot is an overview of the article which represents an example of using Advanced Filter in Excel. You’ll learn more about the dataset as well as the methods & functions to use Advanced Filter in the following sections of this article.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
15 Suitable Uses of Advanced Filter for Multiple Criteria in Excel
1. Advanced Filter for AND Criteria
Let’s get introduced to our dataset first. Column B to Column G represents a number of random computer brands, device types, model names, countries of origin, dates of release, and prices of the devices respectively. Now we’ll use Advanced Filter under different criteria to extract multiple results. We can start with AND logic here. We’re going to extract data for all devices made in the USA and those with prices of not more than $850.
📌 Step 1:
➤ From the Data tab, select the Advanced command from the Sort & Filter group of commands. A dialogue box will appear.
📌 Step 2:
➤ For List Range, select the entire array or the table(B4:G23).
➤ Select the criteria array(E25:F26) for Criteria Range.
➤ Press Ok and you’re done.
Like the picture below, you’ll find the filtered data according to the selected criteria.
To use Advanced Filter effectively, you must have to select the criteria with two rows at least otherwise it won’t work out. For the criteria section in the spreadsheet, you have to use headers for the related columns where filtering criteria will be applied. Advanced Filter will search for the selected criteria under that defined header from the criteria section in your Excel sheet.
Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel
2. Advanced Filter for OR Criteria
Now we’ll filter with OR criteria that means two different criteria will be applied to one single column. Assuming that we want to filter the table for the devices made in Japan and Taiwan only. In the criteria section(E25:E27), Japan and Taiwan are lying in a column under the Country of Origin header. Thus you have to add criteria in a column under the related header for OR logic.
📌 Steps:
➤ The List Range will occupy the range of cells- B5:G23.
➤ Select the range of cells E25:E27 for the Criteria Range.
➤ Press OK.
You’ll get the filtered result like in the following screenshot.
Read More: Advanced Filter with Criteria Range in Excel (18 Applications)
3. Advanced Filter for AND-OR Criteria
We can combine both AND and OR criteria in Advanced Filter. Based on our dataset, we’ll add two different criteria in two columns. We’ll filter the data for the devices made in Japan that cost not more than $800 and the devices made in Taiwan that cost not more than $900. As we’ll follow the similar procedure every time to input List Range and Criteria Range in the Advanced Filter dialogue box, so I’ll mention only List Range as well as Criteria Range from this section to all next ones.
List Range: B4:G23
Criteria Range: E25:F27
And here’s the filtered output in the following picture.
Read More: Excel VBA Examples with Advanced Filter Criteria (6 Cases)
4. Advanced Filter to Show Unique Rows
Our dataset has been modified a bit for this section. There are a number of duplicate rows in the table that I’ve highlighted with different colors. We’ll filter the entire data to show all rows without duplications.
List Range: B4:G23
Criteria Range: (You don’t need to define here now)
Put a mark on the Unique Records option and press OK.
You’ll find the filtered table with all unique rows without any duplications.
Read More: How to Use Advanced Filter for Unique Records Only in Excel
5. Advanced Filter with Wildcards to Find Specific Texts
There are 3 types of wildcard characters in Excel-
? (Question Mark) – Represents any single character in a text.
* (Asterisk) – Represents any number of characters.
~ (Tilde) – Represents the presence of a wildcard character in the text.
By using Asterisk(*) before and after a text in the criteria section, we can search for a specific text string in our table. Assuming, we’re going to find the text “CM” in the column of Model names.
List Range: B4:G23
Criteria range: E25:E26
Like in the picture below, you’ll get the model names with ‘CM’ text inside. By using Asterisks before & after ‘CM’, we’re simply rendering the information to the Excel function that there might be more texts before or after ‘CM’ and thus the function follows the command to show the exact output from the range of cells or an array.
Read More: How to Use Advanced Filter with Wildcard in Excel
6. Advanced Filter for Case-Sensitive Texts
To filter a table with case-sensitive texts, we have to type a formula manually first to execute the function for the 1st row and this formula or function must return with logical values. After that, we’ll have to input this logical value along with a random header in the Criteria Range of the Advanced Filter box. You have to keep in mind, when we’re applying a logical function manually for the 1st row, the header for this criteria must not match with any of the headers present in the original dataset or table.
The related formula to find case-sensitive text in Cell E26 will be:
=EXACT(B5, "Omicron")List Range: B4:G23
Criteria range: E25:E26
There are 5 names with ‘Omicron’ in Column B but they are not case-sensitive. As we’ll extract the data based on exactly ‘Omicron’ with case-sensitive active, by following the mentioned approaches we’ll get the filtered table like the following picture.
Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
7. Advanced Filter for Calculated Results
Now we’ll apply the Advanced Filtered for a calculated data. We’ll find out the devices that cost more than the average price of all values from Column G. So, first of all, we have to type a logical function in Cell E26 for the 1st row that will indicate if the price mentioned in the 1st row is more than the average of all prices or not. The related function will be:
=G5>AVERAGE(G5:G23)List Range: B4:G23
Criteria Range: E25:E26
After filtering data, you’ll get the following result.
Read More: How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)
8. Advanced Filter to Show Rows with Blank Cells
In our dataset, there are some blank cells now. And by using Advanced Filter, we’ll extract those rows containing blank cells. Here in Column E under the table, the related formulas to find blank cells for all columns have been mentioned. And the left column with Blank Cells header represents the criteria range for the Advanced Filter option.
List Range: B4:E23
Criteria Range: D25:D29
After filtering the table with the selected criteria, you’ll find the following results with all rows containing blank cells.
Read More: How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
9. Advanced Filter to Show Rows with Non-Blank Cells
As we’re going to extract the rows with non-blank cells and the criteria has to be applied to 4 different columns separately, so we have to use AND logic here. That means we’re going to add the criteria for 4 columns in a row, not in a column. Under the logical values, you’ll find the formulas that have been used to find return values.
List Range: B4:E23
Criteria Range: C25:F26
The following picture represents the filtered output under the mentioned criteria.
Read More: Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
10. Advanced Filter to Find Top 5 Values
To find the top 5 values from a range of cells with numeric values, we’ll have to type a formula that will search for the value equal to or larger than the 5th largest values from the Price column. So, the related logical function in Cell D26 will be:
=F5>=LARGE($F$5:$F$23,5)List Range: B4:F23
Criteria Range: D25:E26
And the output data will be like in the following picture with the top or highest 5 numerical values.
Read More: Excel Advanced Filter Not Working (2 Reasons & Solutions)
11. Advanced Filter to Find Bottom 5 Values
To get the bottom or lowest 5, the modified formula in Cell D26 will be:
=F5<=SMALL($F$5:$F$23,5)List Range: B4:F23
Criteria Range: D25:E26
After Advanced Filtering, you’ll get the following result with the bottom or lowest 5 numerical values from the Price column.
Read More: How to Use Custom Autofilter in Excel for More Than 2 Criteria
12. Advanced Filter for Matches in the Same Rows
Now there are two columns with the model names of computer devices. We’re going to find the matches in similar rows and extract the filtered data with Advanced Filter. In Cell C26, the related logical function to find matches in the 1st row between two columns will be:
=B5=C5List Range: B4:C23
Criteria Range: C25:C26
The filtered result will look like in the following screenshot:
Read More: How to Remove Duplicate Names in Excel (6 Simple Methods)
13. Advanced Filter for Differences Along Similar Rows
To find the differences in the similar row between two columns, we have to use the ‘Not Equal To’(<>) symbol between two cells for logical function. So, in Cell C26, the related function will be:
=B5<>C5List Range: B4:C23
Criteria Range: C25:C26
And here’s the filtered result for the rows with different texts alongside.
14. Advanced Filter to Find Weekdays
In a modified dataset, two columns are lying with a number of product ID’s along with the assigned delivery dates. Now we’ll filter the dates that include the weekdays only considering the weekends as Saturday & Sunday.
The related logical function to search for the weekday in Cell C26 will be:
=AND(WEEKDAY(C5)<>1,WEEKDAY(C5)<>7)List Range: B4:C23
Criteria Range: C25:C26
Here, we’re adding two different weekday numbers for Saturday & Sunday with AND function. By default, in WEEKDAY function, Sunday starts with 1 & Saturday ends with 7 as weekday numbers. So, by writing a logical function that will not be equal to these two weekday numbers, we can filter the weekdays easily. The following picture represents the filtered data found through the Advanced Filter.
15. Advanced Filter to Filter Weekends
Now to find the weekends, the related logical function in Cell C26 will be:
=OR(WEEKDAY(C5)=1,WEEKDAY(C5)=7)List Range: B4:C23
Criteria Range: C25:C26
You’ll get the filtered results with weekends like in the picture below.
Concluding Words
I hope all of these uses of Advanced Filter mentioned above will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
Related Articles
- How to Remove Advanced Filter in Excel (5 Effective Ways)
- VBA to Copy Data to Another Sheet with Advanced Filter in Excel
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Apply the Advanced Filter to Copy to Another Location in Excel
- How to Create Dynamic Advanced Filter in Excel (2 Applications)
- Use Auto Filter and Advanced Filter in Excel
- How to Use Advanced Filter for Date Range in Excel (2 Easy Ways)
