You can use the Advanced Filter function in Excel to filter the rows in a dataset based on multiple criteria in one column.
The following examples show how to do so with the following dataset in Excel:
Example 1: Advanced Filter with Multiple Criteria in One Column Using OR Logic
Suppose we would like to filter the dataset to only show rows where the value in the Team column is either Mavs or Rockets.
We can define this criteria in the cell range E1:E3 and then click the Data tab and then click the Advanced Filter button:
We’ll choose A1:C12 as the list range and E1:E3 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the team name is Mavs or Rockets:
Example 2: Advanced Filter with Multiple Criteria in One Column Using AND Logic
Suppose we would like to filter the dataset to only show rows where the value in the Points column is greater than or equal to 20 and less than or equal to 30.
We can define this criteria in the cell range E1:F2 and then click the Data tab and then click the Advanced Filter button:
We’ll choose A1:C12 as the list range and E1:F2 as the criteria range:
Once we click OK, the dataset will be filtered to only show rows where the value in the Points column is greater than or equal to 20 and less than or equal to 30:
Note the subtle difference between using OR logic vs. AND logic when using the advanced filter function:
To use OR logic, you must place multiple values in the same column of the criteria range.
To use AND logic, you must place multiple values in different columns of the criteria range.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel Advanced Filter: How to Use “Does Not Contain”
Excel Advanced Filter: How to Use “Contains”
Excel Advanced Filter: Display Rows with Non-Blank Values
