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

Advanced Filter in Excel is one of the coolest options in Excel. Advanced Filter can do many complex filtering that AutoFilter can not do. In this article, we will see How to Use an Advanced Filter if the 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.

Excel Advanced Filter When Criteria Range Contains Text: 5 Ways

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

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

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

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

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.

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

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.

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.

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.

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

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.

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.

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

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.

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

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.

Download Practice Workbook

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.

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