Advanced Filter with Criteria Range in Excel (18 Applications)

In Microsoft Excel, the Advanced Filter option is helpful when looking for data that meets two or more criteria. In this article, we will discuss applications of the Advanced Filter Criteria Range in Excel.


Download Practice Workbook

Download the practice workbook from here.


18 Applications of Advanced Filter Criteria Range in Excel

1. Use of Advanced Filter Criteria Range for Number and Dates

First and foremost, we will get introduced to our dataset. Column B to Column E represents various data associated with sales. Now we can implement here Advanced Filter Criteria Range. In this example, we will use Advanced Filter Criteria Range for filtering numbers and dates. We are going to extract all data where sales quantity is greater than 10. Let’s see the procedure.

Use of Advanced Filter Criteria Range for Number and Dates

  • Firstly, in the Data tab, select the Advanced command from the Sort & Filter option. A dialogue box named Advanced Filter will appear.

Use of Advanced Filter Criteria Range for Number and Dates

  • Next, select the entire table (B4:E14) for the List range.
  • Select cell (C17:C18) as Criteria range.
  • Press OK.

Use of Advanced Filter Criteria Range for Number and Dates

  • Finally, we can see only the data having quantities larger than 10.

Note:
1. Select the criteria with two rows at least.

2. We will use headers for the related columns where filtering criteria will be applied.


2. Filter Text Value with Advanced Filter Criteria

We can compare text values using logical operators in addition to numbers and dates. In this section, we will discuss how we can filter text value with Advanced Filter Criteria for an exact match of Text as well as having a specific character at the beginning.

2.1 For Exact match of Text

In this method, Filtering will return us the exact value of the input text. Suppose we have the following dataset of sales along with a new column City. We will extract only the data for the city ‘NEW YORK’. Just do the following steps to perform this action:

  • In the beginning, select cell C18. Insert the following formula:
=EXACT(D5," NEW YORK")
  • Press Enter.

For Exact match of Text

  • Next, select the following filter criteria range:

List Range: B4:F14

Criteria Range: C17:C18

  • Hit OK.

For Exact match of Text

  • Lastly, we will get only the data for the city ‘NEW YORK’.


2.1 Having Specific Character at the Beginning

Now we will filter text values for starting with a specific character rather than an exact match. Here, we will extract only the values of cities starting with the word ‘New’. Let’s see how to do that.

Having Specific Character at the Beginning

  • Firstly, select the criteria ranges in the Advanced Filter box:

List Range: B4:F14

Criteria Range: C18:C19

  • Press OK.

Having Specific Character at the Beginning

  • Finally, we will get the data for all cities starting with the word ‘New’.


3. Use Wildcards with Advanced Filter Option

The use of wildcard characters is another way to apply Advanced Filter Criteria Range. Usually, there are three 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.

We can search for a specific text string in our dataset by using Asterisk (*). In this example, we find the names of salespeople starting with the text ‘J’. To do that, we need to follow these steps.

Use Wildcards with Advanced Filter Criteria Range

  • First, open the Advanced Filter window. Select the following criteria range:

List Range: B4:F14

Criteria Range: C17:C18

  • Press OK.

Use Wildcards with Advanced Filter Criteria Range

  • Finally, we will get the names of salespeople only that start with text ‘J’.

Related Contents: Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]


4. Apply Formula with Advanced Filter Criteria Range

One more way to use Advanced Filter Criteria Range is to apply formulas. In this example, we will extract the sales amount greater than $350. Just follow the below steps to this:

  • In the beginning, select cell C19. Insert the following formula:
=F5>350
  • Hit OK.Apply Formula with Advanced Filter Criteria Range

The formula iterates the value of sales amount whether it is greater than $350 or not.

Apply Formula with Advanced Filter Criteria Range

  • Next, select the following criteria range in the Advanced Filter dialogue box:

List Range: B4:F14

Criteria Range: C17:C19

  • Press OK.

  • So, we can see the data for only the values of sales greater than $350.

5. Advanced Filter with AND Logic Criteria

We will now introduce AND logic in the Advanced Filter Criteria Range. This logic uses two criteria. It returns the output value when data satisfies both criteria. Here we have the following dataset. In this dataset, we will filter data for the city of New York as well as having sales value >= 200. Let’s see how to do that.

Advanced Filter Criteria Range with AND Logic

  • First, go to the Advanced Filter dialogue box select the following criteria range:

List Range: B4:F14

Criteria Range: C18:C19

  • Press OK.

Advanced Filter Criteria Range with AND Logic

  • Finally, we will get the dataset for only the city of New York having a sales value greater than $250.


6. Use of OR Logic with Advanced Filter Criteria Range

Like the AND logic, the OR logic uses two criteria also. AND logic returns output if both criteria are fulfilled whereas OR logic returns if only one criterion is fulfilled. Here we will data for the cities New York and Texas only. Just follow the below steps to perform this action:

Use of OR Logic with Advanced Filter Criteria Range

  • In the beginning, open the Advanced Filter dialogue box. Input the following criteria range:

List Range: B4:F14

Criteria Range: C18:C20

  • Hit OK.

Use of OR Logic with Advanced Filter Criteria Range

  • Finally, we get the dataset only for the cities New York and Texas.


7. Combination of AND & OR Logic as Criteria Range

Sometimes we may need to filter data for multiple criteria. In that case, we can use a combination of AND & OR logic. We will extract data from the following dataset based on the given criteria. Just do the following steps to perform this action:

Combination of AND & OR Logic in Advanced Filter Criteria Range

  • Firstly, open the Advanced Filter dialogue box. Select the following criteria:

List Range: B4:F14

Criteria Range: C18:C20

  • Then press OK.

Combination of AND & OR Logic in Advanced Filter Criteria Range

  • So, we can see the dataset only which matches our criteria.

8. Using Advanced Filter Criteria Range to Extract Specific Columns

In this example, we will filter specific parts of a dataset. After filtering we will move the filtered part into another column. We will use the following dataset to perform this action through the below procedure.

Using Advanced Filter Criteria Range to Extract Specific Columns

  • First, from the Advanced Filter dialogue box select the following criteria:

List Range: B4:F14

Criteria Range: C18:C20

  • Select copy to another location option.
  • Input Copy to range H8:I10.
  • Hit OK.

Using Advanced Filter Criteria Range to Extract Specific Columns

  • So, we get the filtered data in H8:I10 according to our criteria.


9. Copy Data to Another Worksheet after Filtering

In this example, we will also copy data in another worksheet whereas in the previous example we did it in the same worksheet. Do the following steps to execute it:

  • First, go to ‘Another Worksheet-2’ where we will copy data after filtering.

Copy Data to Another Worksheet after Filtering

We can see two columns ‘City’ and ‘Sales’ in ‘Another Worksheet-2’.

Copy Data to Another Worksheet after Filtering

  • Next, open the ‘Advanced Filter’ dialogue box.

Copy Data to Another Worksheet after Filtering

  • Then go to ‘Another Worksheet-1’. Select the following criteria:

List Range: B4:F14

Criteria Range: C18:C19

  • Now, select copy to another location option.

Copy Data to Another Worksheet after Filtering

  • After that, go to ‘Another Worksheet-2’. Select Copy to Range B2:C4.
  • Press OK.

  • Finally, we can see the filtered data in ‘Another Worksheet-2’.


10. Extract Unique Records with Advanced Filter Criteria

In this case, we will extract only the unique values from a specific column. From the following dataset, we will extract unique values of cities in another column. Just do the steps:

Extract Unique Records with Advanced Filter Criteria

  • In the beginning, open the Advanced Filter window. Select the criteria

List range: D4:D14

  • Next, select the option Copy to another location.
  • Then, input Copy to range as H4:H8.
  • Check the box Unique records only.
  • Press OK.

Extract Unique Records with Advanced Filter Criteria

  • Finally, we can see the names of cities with unique records only in column H.


11. Find Weekdays with Advanced Filter Criteria Range

We can find Weekdays with Advanced Filter Criteria Range. Here we will use the following dataset to illustrate this process:

  • Firstly, select cell C19. Insert the following formula:
=AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7)

Find Weekdays with Advanced Filter Criteria Range

  • Next, set the following criteria range in the Advanced Filter dialogue box:

List Range: B4:F14

Criteria Range: C18:C19

  • Press OK.

Find Weekdays with Advanced Filter Criteria Range

  • Finally, we will get the Date values only for weekdays.

🔎 How Does the Formula Work?

  • WEEKDAY(B5)<>1: 1 denotes Sunday. This part set the criteria that the date is not Sunday.
  • WEEKDAY(B5)<>7: 7 denotes Sunday. This part set the criteria that the date is not Saturday.
  • AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7): Set the criteria that the day is neither Saturday nor Sunday.

12. Apply Advanced Filter to Find Weekend

We can also use the Advanced Filter Criteria Range to find the Weekend from a Date column. Let’s see how to do that using the following dataset:

  • In the beginning select cell C19. Insert the following formula:
=OR(WEEKDAY(B5)=1,WEEKDAY(B5)=7)
  • Press Enter.

Apply Advanced Filter Criteria Range to Find Weekend

  • Next, from the Advanced Filter dialogue box select the following criteria range:

List Range: B4:F14

Criteria Range: C18:C19

  • Press OK.

Apply Advanced Filter Criteria Range to Find Weekend

  • So, we can see only the values of the weekend in the Date column.


13. Use Advanced Filter to Calculate Values Below or Above Average

In this section, we will calculate the below or above average value by using Advanced Filter Criteria Range. Here we will only filter the sales value which is greater than the average sales value.

  • First, select cell C19. Insert the following formula:
=E5>AVERAGE(E5:E14)

Use Advanced Filter Criteria Range to Calculate Values Below or Above Average

  • Next, open the Advanced Filter dialogue box. Input the following criteria range:

List Range: B4:F14

Criteria Range: C18:C19

  • Press OK.

Use Advanced Filter Criteria Range to Calculate Values Below or Above Average

  • So, we get only the dataset for sales value greater than the average value.


14. Filtering Blank Cells with OR Logic

If our dataset consists of blank cells, we can extract blank cells by using Advanced Filter.

We have the following dataset. The dataset consists of blank cells. We have set the criteria by using the following formula:

=B5=""

Filtering Blank Cells with OR Logic

  • First, go to the Advanced Filter dialogue box. Input the following criteria:

List Range: B4:F14

Criteria Range: C17:C22

  • Press OK.

Filtering Blank Cells with OR Logic

  • Finally, we get the dataset that only consists of blank cells.


15. Apply Advanced Filter to Filter Non-Blank Cells using OR as well as AND Logic

In this example, we will eliminate blank cells whereas in the previous example we eliminated the nonblank cells. We have set the following criteria for using the formula:

=B5<>""

Apply Advanced Filter Criteria Range to Filter Non-Blank Cells using OR as well as AND Logic

  • Firstly, go to the Advanced Filter dialogue box. Insert the following criteria range:

List Range: B4:F14

Criteria Range: C17:G18

  • Now press OK.

Apply Advanced Filter Criteria Range to Filter Non-Blank Cells using OR as well as AND Logic

  • So, we get the dataset free from blank cells.


16. Find First 5 Records Using Advanced Filter Criteria Range

Now we will implement the Advanced Filter option for extracting the first 5 records from any kind of dataset. In this example, we will take the first five values of the Sales column. To perform this we will first set the criteria based on the following formula:

=F5>=LARGE($F$5:$F$14,5)

Find First 5 Records Using Advanced Filter Criteria Range

After that, just do the following steps:

  • In the beginning, go to the Advanced Filter dialogue box. Insert the following criteria range:

List Range: B4:F14

Criteria Range: C17:C18

  • Hit OK.

Find First 5 Records Using Advanced Filter Criteria Range

  • Finally, we get the top five records of the Sales column.


17. Use Advanced Filter Criteria Range to Find Bottom Five Records

We can use the Advanced Filter option to find the bottom five records also. To find the bottom five records for the Sales column, we will create the following criteria using the below formula:

=F5<=SMALL($F$5:$F$14,5)

Use Advanced Filter Criteria Range to Find Bottom Five Records

Then follow the below steps to perform this action:

  • First, insert the following criteria range in the Advanced Filter dialogue box:

List Range: B4:F14

Criteria Range: C17:C18

  • After that, press OK.

Use Advanced Filter Criteria Range to Find Bottom Five Records

  • Lastly, we can see the bottom five values of the Sales column.


18. Filter Rows According to a List’s Matched Entries Using Advanced Filter Criteria Range

Sometimes we may need to compare between two columns or rows of a dataset to eliminate or keep particular values. We can use the match entry option to perform this kind of action.

18.1 Matches with Items in a List

Suppose we have the following dataset with two columns of cities. We will take only the matching entries between these two columns. In order to do this we will set the following criteria using the below formula:

=C5=E5

Matches with Items in a List

Just do the following steps to perform this action:

  • In the beginning, open the Advanced Filter option. Insert the following criteria range:

List Range: B4:F14

Criteria Range: C17:C18

  • Hit OK.

Matches with Items in a List

  • Lastly, We can see the same value in two columns of cities.


18.2 Do Not Matches with Items in a List

The previous example was for matching entries whereas this example will filter non-matching entries. We will set the criteria by using the following formula:

=C5<>E5

Do Not Matches with Items in a List

Let’s see how to perform this:

  • First, from the Advance Filter insert the following criteria range:

List Range: B4:F14

Criteria Range: C17:C18

  • Then, press OK.

Do Not Matches with Items in a List

  • Finally, we will get the values of cities in Column C and Column E that do not match with one another.


Conclusion

In this article, we have tried to cover all the methods of the Advanced Filter Criteria Range option. Download our practice workbook added to this article and practice yourself. If you feel any confusion or have any suggestions just leave a comment below, we will try to reply to you as soon as possible.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo