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

Advance Filter in Excel is one of the coolest options in Excel. Advance Filter can do many complex filtering that AutoFilter can not do. In this article, we will see How to Use Advanced Filter If Criteria Range Contains Text in Excel. We will use a sample dataset for convenience which contains Date, Name, Item, and Sales. Using this dataset we will extract data for several complex criteria.

Advance Filter Text Criteria


Download Practice Workbook


5 Ways of Using Excel Advanced Filter When Criteria Range Contains Text

Throughout this post, we will get to know about AND, OR, and Wildcard characters for filtering data and the use of wildcard characters for text criteria.


Method 1: Advanced Filter for Cell That Contains Unique Text Values

As you can see, in the dataset we have some duplicate values. We will see how to remove these duplicates and generate a unique dataset.

Steps:

  • First, click on the Data tab and choose Advance Filter or press ALT+A+Q

Advance Filter Text Criteria unique data

  • Now, a dialogue box will pop up and we will select Copy to another location, then our data range $B$4:$E$14, then in Copy to the section, we will select a cell where we want to copy our unique values, at last click on Unique record only and click OK.

Advance Filter Text Criteria unique dataset

As a result, our dataset will look like the following image now.

You can see, there are no duplicate values, only unique text records.

Read More: How to Use Advanced Filter for Unique Records Only in Excel


Method 2: Advanced Filter for Cells Whose Values are Exactly Equal to Text Criteria

Suppose, we want to extract data for a name that contains Brad and has a Sales value greater than $50.

Steps:

  • First, we will create a dataset similar to the original column. Like the image below.

  • Now, go to the Data tab and click Advance Filter or press ALT+A+Q.

Advance Filter Text Criteria exact match

  • Now, a dialogue box will pop up and we will do as the following image.

Advance Filter Text Criteria exact text match

Here, first of all, we selected Copy to another location, otherwise, the dataset will be filtered in its exact location, then selected the List Range including Header, after that, we selected the Criteria range, as earlier we created a similar subset for the data and finally selected the area where we wanted our records and clicked OK.

As you can see, the Advanced Filter extracted data exactly what we wanted.

Read More: How to Apply the Advanced Filter to Copy to Another Location in Excel


Method 3: Advanced Filter for Text Values with Wildcard Characters

We can use three wildcard characters asterisk (*), question mark (?), and tilde (~) while constructing criteria, let’s see how to use these.


3.1: Filter Cells That Begin with the Text

Suppose, we want to extract names that begin with Leo.

Steps:

  • First, we will create a dataset similar to the original column. Like the image below.

Advance Filter Text Criteria Begin With

You can see, we put an asterisk sign after Leo, like this Leo*.

  • Now, press ALT+A+Q or go to the Data tab and select Advance Filter.

Advance Filter Text Criteria asterisk

  • So, a dialogue box will pop up and fill the criteria as shown in the image below.

Advance Filter Text Criteriawildcard character

Here, first of all, we selected Copy to another location, otherwise, the dataset will be filtered in its exact location, then selected the List Range including header, after that, we selected the Criteria range, as earlier we created a similar subset for the data and finally selected the area where we wanted our records and clicked OK.

We have a Name value Johnson Leo, but it doesn’t start with Leo, that’s why we didn’t get it in our extracted data.


3.2: Filter Cells Using Question Mark

Now, we want all the items that start with Sh and then have another letter after that and the following letters rt. Simplifying Shirt and Shorts.

Steps:

  • First, we will create a dataset similar to the original column. Like the image below.

After that, follow Method 3.1 and the result will be as follows.

Read More: Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]


Similar Readings


Method 4: Advanced Filter for Text Values with AND Rule

When using AND logic we have to keep our wanted data side by side. Suppose, we want data where names have Leo in it and the item is pant. So, let’s see how we will do it.

Steps:

  • First, we will create a dataset similar to the original column. Like the image below.

  • Now, press ALT+A+Q and a dialogue box will up. From there we will do as the following image shows.

Advance Filter Text Criteria AND rule

  • So, how to fill the List range, Criteria range, you already know. Yeah, the similar task we’ve done in Method 2.

Finally, our dataset will look like the following image.

Related Content: Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)


Method 5: Advanced Filter for Text Criteria with OR Rule

We will now see the use of OR logic. Now, we want to know Brad in Name or Item as Shirt, Shorts or Trouser. Any of the data in the row or column means valuable for us. So, let’s get into this.

Steps:

  • First, we will create a dataset similar to the original column. Like the image below.

Advance Filter Text Criteria OR logic

  • Now, you know, how to use Advanced Filter from now on. You can get help from Method 2. Our final result will look like the following image.

Read More: How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

Advance Filter Text Criteria


Conclusion

These are 5 different methods for using Excel Advanced Filter with Criteria Range Contains Text. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback


Related Articles

Mahbub
2 Comments
  1. Hi, I have two queries.
    1. How to use advanced filter by the criteria of Date? I mean if I want to filter data of a certain month how can it be done by using advanced filter?

    2. How to use advanced filter by the contains criteria? suppose if I want to filter data which contains ‘letter A’ or have ‘5’ how can it be done?

    thank you.

    • Hi Shilpa, thanks for the response. Here’s the solution to your question no 1

      You can simply create a criteria similar to the methods of this article using dates. Suppose you want to see the sales information after May. Please watch the following image for the process. I created the criteria in G6 cell.

       

      In order to solve the second question of your comment, please apply the method described in the Section 3.2 of this article.
      Hope this helps to solve your queries.

Leave a reply

ExcelDemy
Logo