How to Use an Advanced Filter If the Criteria Range Contains Text in Excel – 5 Methods

This is the sample dataset.

Method 1 – Using an Advanced Filter for a Cell That Contains Unique Text Values

The dataset contains duplicate values.

Steps:

• Click the Data tab and choose Advance Filter or press ALT+A+Q

• In the dialog box, select Copy to another location.
• In List range, enter $B$4:$E$14.
• In Copy to, select a cell to copy the unique values.
• Check Unique records only.
• Click OK.

This is the output.

There are no duplicate values, only unique text records.

Method 2 – Using an Advanced Filter for Cells Whose Values are Exactly Equal to the Text Criteria

To extract data for a name that contains Brad and has a Sales value greater than $50.

Steps:

• Create a similar dataset, as shown below.

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

• In the dialog box, select Copy to another location.
• In List range, enter $B$4:$E$14.
• Enter the Criteria range.
• In Copy to, select a cell.
• Click OK.

This is the output.

Method 3: Using an Advanced Filter for Text Values with Wildcard Characters

3.1: Filter Cells That Begin with a specific Text

To extract names that begin with Leo.

Steps:

• Create a similar dataset, as shown below.

• Enter an asterisk after Leo.
• Press ALT+A+Q or go to the Data tab and select Advanced Filter.

• Select Copy to another location.
• Enter the List Range.
• Enter the Criteria range.
• In Copy to, select a cell.
• Click OK.

This is the output.

3.2: Filter Cells Using a Question Mark

Filter the items that start with Sh, have another letter after it and include rt.

Steps:

• Create a similar dataset, as shown below.

• Follow the steps described in Method 3.1.

This is the output.

Method 4 – Using an Advanced Filter and the AND Rule for Text Values

Extract data with the name Leo and the item pant.

Steps:

• Create a similar dataset, as shown below.

• Press ALT+A+Q.

• Select Copy to another location.
• Enter the List Range.
• Enter the Criteria range.
• In Copy to, select a cell.
• Click OK.

This is the output.

Method 5 – Using an Advanced Filter with the OR Rule for Text Criteria

Find the name Brad or the Items Shirt, Shorts or Trouser.

Steps:

• Create a similar dataset, as shown below.

• Follow the steps in Method 2.

This is the output.

Practice Section

Practice here.

Download Practice Workbook

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