Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 good idea3


Exctract Email Address (extract all the email addresses from a cell or a range to another location.)

doc extract email address

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.
doc filter by criteria wildcard 1

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:

doc filter by criteria wildcard 2

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:

doc filter by criteria wildcard 3
doc filter

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

doc filter by criteria wildcard 5

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:

doc filter by criteria wildcard 6

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

doc filter by criteria wildcard 7

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

doc filter by criteria wildcard 8


Filter with wildcard

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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:

doc filter by criteria wildcard 9

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.

doc filter by criteria wildcard 10

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

doc filter by criteria wildcard 11

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

doc filter by criteria wildcard 12

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.

doc filter by criteria wildcard 13

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

doc filter by criteria wildcard 14

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

doc filter by criteria wildcard 15

The ~ wildcard

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

doc filter by criteria wildcard 16

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.

doc filter by criteria wildcard 17

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

doc filter by criteria wildcard 18

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

doc filter by criteria wildcard 19

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.
doc filter by criteria wildcard 20

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

doc filter by criteria wildcard 21

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 60 days.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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

Multiple criteria

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

doc kte filter by criteria wildcard 1

2. Then you need to click this button doc select range to select a range that you want to filter. See screenshot:

doc kte filter by criteria wildcard 2

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.

doc kte filter by criteria wildcard 3

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

doc kte filter by criteria wildcard 4

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:

doc kte filter by criteria wildcard 5

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

doc kte filter by criteria wildcard 6

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

doc kte filter by criteria wildcard 7doc arrow rightdoc kte filter by criteria wildcard 8

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

doc kte filter by criteria wildcard 9   doc kte filter by criteria wildcard 7doc arrow rightdoc kte filter by criteria wildcard 11

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:

doc kte filter by criteria wildcard 12   doc kte filter by criteria wildcard 13doc arrow rightdoc kte filter by criteria wildcard 14

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

doc kte filter by criteria wildcard 15    doc kte filter by criteria wildcard 16doc arrow rightdoc kte filter by criteria wildcard 17

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.

pay attention1If you are interested in this addi-in, click here to download the 60-days free trial.

With Super Filter, you also can do:

Filter by asterisk or other special character in Excel

Filter by substring in Excel

Paste to Visible Range (copy and paste data in visible or filtered cells only.)

excel addin tool for paste data to filtered cells only and ignore hidden cells


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.