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
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.