Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

advanced filter with multiple criteria overview

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.


Advanced Filter with Multiple Criteria in Excel: 15 Examples

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.

advanced filter and logic criteria in excel

📌 Step 1:

➤ From the Data tab, select the Advanced command from the Sort & Filter group of commands. A dialogue box will appear.

advanced filter and logic criteria in excel

📌 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.

advanced filter and logic criteria in excel

Like the picture below, you’ll find the filtered data according to the selected criteria.

advanced filter and logic criteria in excel

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.


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.

advanced filter or logic criteria in excel

You’ll get the filtered result like in the following screenshot.

advanced filter or logic criteria in excel


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

advanced filter and or logic criteria in excel

And here’s the filtered output in the following picture.

advanced filter and or logic criteria in excel


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.

advanced filter unique rows in excel

You’ll find the filtered table with all unique rows without any duplications.

advanced filter unique rows 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

advanced filter with wildcard to find specific text in excel

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.

advanced filter with wildcard to find specific text 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

advanced filter case sensitive criteria in excel

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.

advanced filter case sensitive criteria in excel

Read More: How to Use Advanced Filter If Criteria Range Contains Text in Excel


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

advanced filter for calculated data in excel

After filtering data, you’ll get the following result.

advanced filter for calculated data in excel


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

advanced filter to show rows with blank cells in excel

After filtering the table with the selected criteria, you’ll find the following results with all rows containing blank cells.

advanced filter to show rows with blank cells in excel


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

advanced filter to show rows with non blank cells in excel

The following picture represents the filtered output under the mentioned criteria.

advanced filter to show rows with non blank cells in excel


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

advanced filter to show top 5 in excel

And the output data will be like in the following picture with the top or highest 5 numerical values.

advanced filter to show top 5 in excel


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

advanced filter to show bottom or lowest 5 in excel

After Advanced Filtering, you’ll get the following result with the bottom or lowest 5 numerical values from the Price column.

advanced filter to show bottom or lowest 5 in excel


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=C5

List Range: B4:C23

Criteria Range: C25:C26

advanced filter to find matches in same rows in excel

The filtered result will look like in the following screenshot:

advanced filter to find matches in same rows in excel


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<>C5

List Range: B4:C23

Criteria Range: C25:C26

advanced filter to find differences along rows in excel

And here’s the filtered result for the rows with different texts alongside.

advanced filter to find differences along rows in excel


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 functions we have used in Cell C26 are WEEKDAY with AND to search for the weekday:

=AND(WEEKDAY(C5)<>1,WEEKDAY(C5)<>7)

List Range: B4:C23

Criteria Range: C25:C26

advanced filter to find weekdays in excel

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.

advanced filter to find weekdays in excel


15. Advanced Filter to Filter Weekends

Now to find the weekends, we have used WEEKDAY with the OR function in Cell C26:

=OR(WEEKDAY(C5)=1,WEEKDAY(C5)=7)

List Range: B4:C23

Criteria Range: C25:C26

advanced filter to find weekends in excel

You’ll get the filtered results with weekends like in the picture below.

advanced filter to find weekends in excel


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


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.


<< Go Back to Advanced Filter | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo