How to filter with multiple criteria in excel?
Data filters based on one criteria may be easy for most users.How can you filter the data with multiple criteria?In this video, you will learn how to set multiple criteria and filter data in excel.
I have a duty to tell you that the setting of multiple criteria is not easy to set in the excel filters.If you want to save time, I suggest you use Super Filter, which will make it easy to understand.
For example, if you want to filter out the data which meets all criteria at the same time, you should consider the fact that the region is north and the total is larger than 2000.You need to list the criteria in the cells.
You can specify the List range and Criteria range in the Advanced Filter dialog after you click Data > Advanced.You can see the screenshot.
If you want to exclude the data that doesn't meet one of the criteria, like the total number is larger than 2000 or the region is north, you can list them.
In some cases, you might want to use a special text string.You have a range of data.
You must list this criterion in cells if you want to filter out the data which ends with Market in the partner column.
In the Advanced Filter dialog, specify the List range and Criteria range.You can see the screenshot.
For example, if you want to filter out the data which only contains a character between two text strings, you can use the criteria below.
In the Advanced Filter dialog, specify the List range and Criteria range.You can see the screenshot.
You can use the filters below to remove the symbol or other special mark from the data.
In the Advanced Filter dialog, specify the List range and Criteria range.You can see the screenshot.
If you want to filter the data, you can do it by selecting them and clicking Data > Filter and then clicking the filter arrow.
The criteria can be typed into the text box next to the contains.You can see the screenshot.
The settings of the Filter function are listed here.Is it hard to remember?You don't need to remember the complex criteria if you have the Super Filter of Kutools.
Super Filter is an advanced function of Kutools that is easy to use.You can use Super Filter and download Kutools for free.Kutools is free for 30 days.
2.To fix the range, you need to click this button and select a range that you want to filter.You can see the screenshot.
3.The data should be sorted by multiple criteria.You have to make sure that the relationship is OR or AND.
When the combination of multiple criteria is true, you need a relationship.
For example, you want to know if the region is south, apple and the total number is greater than 1100.
Set the first criterion you need in the textboxes when you select AND from the Relationship in Group list.Screenshots:
You need to make sure that all the relationships are OR.Screenshots:
You can use a string of characters or a special symbol in the Super Filter function.
If you want to filter out data which contains supplier, you just need to type *supplier into the last textbox.Screenshots:
The question mark means anyone character, so if you need to filter out data which is in this format, you just have to type this into the textbox of Super Filter.You can see the screenshot.