How to filter with multiple criteria or wildcard in Excel?

For most of Excel users, data filtering based on one criteria may be easy. However, if you want to filter data with multiple criteria, how can you do? This tutorial is talking about how to set multiple criteria and filter data in Excel

Filter with multiple criteria

Filter with wildcard

Easily filter data with multiple criteria or wildcard with Super Filter

Filter with multiple criteria

Before you reading the detail steps in this part, I have the duty to tell you that filtering with multiple criteria or wildcard by Excel Filter is a little complex because of the not simple multiple criteria setting. If you want to save time, I suggest you go to Easily filter data with multiple criteria or wildcard with Super Filter, which will easy understanding.

Suppose you have a range of data as below screenshot shown, and you want to do some filters on it.

And vs Or

For example, you want to filter out the data which meets all criteria at the same while, take instance, the data which region is north and the product is banana and the total is larger than 2000. Now you need to list the criteria in the cells as below screenshot shown:

Then you can click Data > Advanced, and in the Advanced Filter dialog, check Filter the list, in-place, and specify the List range and Criteria range. See screenshot:

Click OK. Now you can see the data has been filtered based on the criteria you made.

Or otherwise, if you want to filter out the data which just need to meet one of the criteria, take instance, the region is north, or the product is banana, or the total number is larger than 2000, you can list the criteria like this:

Then click Data > Advanced and specify the List range and Criteria range, see screenshot:

Click OK. Now you can see all data which meets one of the criteria is filtered out.

Filter with wildcard

In some cases, you may want to filter data based on a special text string. For example, you have a range of data as below screenshot shown:

The * wildcard

And you want to filter out the data which ends with Market in the Partner column, and you must list this criterion in cells as below screenshot shown:

Tip: the asterisk symbol * stands any character string in its position.

Then click Data > Advanced, and in the Advanced Filter dialog, specify the List range and Criteria range. See screenshot:

Now click OK, you can see all data ending with Market is filtered out.

The ? wildcard

If you want to filtered out the data which only contains a character between two specific text strings, for example, filtered out the data has any character between c and k, you can type the criteria as below:

Tip: The question mark ? represents anyone character in its position.

Then click Data > Advanced, and in the Advanced Filter dialog, specify the List range and Criteria range. See screenshot:

Click OK. And you can see the data is filtered out.

The ~ wildcard

If you have a range of data as below screenshot shown:

And you want to filter out the data containing * symbol or other special mark between two specific text strings, you can do as below:

Tip: The tilde ~ mark can help you to search the specific text string.

Then click Data > Advanced, and in the Advanced Filter dialog, specify the List range and Criteria range. See screenshot:

Click OK to close the dialog, then you can see the data is filtered out:

Note:

Actually, you also can filter the data by selecting them and clicking Data > Filter and then clicking the filter arrow and select Text Filter or Number Filter > Contains.

Then in the Custom AutoFilter dialog, type the criteria into the text box next to contains. See screenshot:

And click OK. The data also can be filtered out correctly.

That are all the criteria settings of Filter function. Is it hard to remember? But if you have Super Filter of Kutools for Excel, you never need to remember the complex criteria, you just need to click, click, click.

Easily filter data with multiple criteria or wildcard with Super Filter

Super Filter is an advanced filtering function of Kutools for Excel, which is easily get and use. You can free download Kutools for Excel and use Super Filter by follow steps. Do not worry, Kutools for Excel is free for 30 days.

After free installing Kutools for Excel, please do as below:

Multiple criteria

1. Click Kutools Plus > Super Filter to enable the Super Filter pane. See screenshot:

2. Then you need to click this button  to select a range that you want to filter, then check Specified checkbox to fix the range. See screenshot:

3. Now begin to filter the data by multiple criteria. Firstly, you need to make sure that the Relationship is OR or AND.

If you want to filter data when the combination of multiple criteria is true, you need AND relationship.

If you filter data out which only need to meet one of the criteria, you select OR.

4. Then you can list the criteria in the pane.

And and Or

For example, you want to filter data that the region is south, product is apple and the total number is greater than 1100, do as below:

(1) Select AND from the Relationship in Group list and click at the first underline next to And, then set the first criterion you need in the textboxes. See screenshots:

(2) Then set the second and third criteria as above. See screenshot:

5. Click Filter button then the data is filtered out.

If you select OR relationship, you need to make sure that all the relationships are OR. See screenshots:

Wildcard

In Super Filter function, you also can use wildcard to represent a character or a string of characters or a special symbol.

Take an instance, you want to filter out data which contains supplier, you just need to type *supplier (the asterisk mark means any strings) into the last textbox. See screenshots:

If you need to filter out data which is in this format c?ke, (the question mark means anyone character), you just need to type this into the textbox of Super Filter, and click Filter. See screenshot：

Is it so easy? If you want to know more about Super Filter, you can free download Kutools for Excel to try it by yourself, there are 60 days of free trail. Actually, Kutools for Excel has more than 100 functions which can make your daily work easier.

Demo: Filter With Multiple Criteria And Wildcard with Kutools for Excel

With Super Filter, you also can do:

Filter by asterisk or other special character in Excel

Filter by substring in Excel