Al Arafat Siddique
In this article, we’ll illustrate how to filter one single column based on multiple criteria using the advanced command in Excel with 5 different methods. The advanced filter in Excel allows us to apply different logics to filter a dataset to a great extent. Let’s dive into the examples to get a good understanding of this useful feature.
In this article, to show the advanced filter based on multiple criteria in one column, we’ll use the following dataset. The dataset contains a list of sale data for products of different categories in several cities with their sold quantity. We’re going to apply multiple filtering criteria using the advanced filter function in a specific column.
1. Use of Advanced Function to Filter One Column Based on Multiple Criteria in Excel
In this example, we’ll filter the dataset only for products that belong to the Bars or Snacks categories. We defined the criteria range with the same header name as “Category”.
Now follow the steps to filter the dataset for products of Bars and Snacks categories only.
- Select the whole dataset.
- Then go to the Data tab from the Excel Ribbon.
- Click the Advanced button.
- In the following screenshot, the Advanced Filter window showed the selected List Range $B$8:$F$27e., the whole dataset.
- Now click on the arrow button on the right side of the Criteria Range input box.
- By dragging the mouse, select the criteria range e., cells D4:D6, and then press Enter.
- After pressing Enter the Advanced Filter window popped up again where we see the List range and the Criteria range as selected. Make sure the “Filter the list, in-place” option is selected.
- Finally, hit OK to save.
- We’ve successfully filtered the dataset for products of categories Bars or Snacks.
Read More: Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
2. Filter One Column Using Advanced Command Based on Multiple Criteria with Helper Column
To filter a column with multiple criteria, we’ll use a helper column in this illustration. Follow the following steps:
- Add a new column named “Helper Column”.
- In cell G9, put the following formula.
=COUNTIF($D$5:$D$6,D9)Here, the COUNTIF function checks the category in cell D9 (Bars) in the criteria range D5:D6 (Bars and Snacks). If it finds a match, it returns true, otherwise false. Likewise, we’ll check each category name against the criteria list. That’s why we used absolute reference for the criteria range.
- Press Enter.
- Now, locate the Fill Handle at the right bottom corner of cell G9 and drag it down to the last row of the dataset.
- The above action copied the formula to all cells of the helper column. In the following screenshot, we see that the rows with products of categories Bars or Snacks have 1 as output in the helper column otherwise 0.
- Now select the helper column.
- Go to the Data tab from the Excel Ribbon.
- Click on the Filter option.
- A down arrow appeared at the right corner of the helper column header. Click on the arrow and select the checkbox with number 1 and hit OK.
- Finally, we have our filtered dataset for the categories Bars and Snacks.
Read More: Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)
Similar Readings
- How to Use Advanced Filter If Criteria Range Contains Text in Excel
- Apply the Advanced Filter to Copy to Another Location in Excel
- How to Remove Duplicate Names in Excel (6 Simple Methods)
- Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
- How to Use Custom Autofilter in Excel for More Than 2 Criteria
3. Apply Excel Advanced Command to Filter Unique Values Based on Multiple Criteria in One Column
Let’s say, we want to find out the names of the unique products that belong to 4 different categories in the following dataset.
To accomplish this, we need to follow the steps in example 1 and set-
List range: $B$9:$C$28
Criteria range: $B$3:$B$7
And click the checkbox named “Unique records only”.
In the final output, we have 7 unique products from 4 different categories. This way we can modify the filtering criteria to know the unique product names of those categories.
Read More: How to Use Advanced Filter for Unique Records Only in Excel
4. Advanced Filter with Wildcards Based on Multiple Criteria in One Column
We have 3 types of wildcards in Excel to use. We can use-
* (Asterisk) to find any number of characters in a text,
? (Question Mark) to find a single character in a text and
~ (Tilde) to find any wildcard character in a text.
In this example, we want to find products having either Chip or Potato strings in their names.
So we need to use * (Asterisk) to enclose these two strings in the filtering criteria. Following the steps in Example 1, we need to set the List range as $B$7:$B$26 and the Criteria range as $D$2:$D$4. In addition, we need to click the checkbox named “Unique records only”.
As an output, we’ve got two products Chocolate Chips and Potato Chips.
In the following screenshot, we’ve shown the products that have either C at the beginning or Potato in their name.
Read More: Advanced Filter with Criteria Range in Excel (18 Applications)
5. Advanced Filter for Calculated Data in One Column Based on Multiple Criteria in Excel
In this example, we’ll apply multiple criteria on one column using calculated data. Here, we’re going to find the products with quantity more than 50 but less than 100. For this, we need to apply the following formula for the first cell (F4) of the Quantity column and get the output result in a random cell (E22, in this example) with a random header (should not match with the header names of the dataset). The formula is-
=IF(AND(F4<100,F4>50),F4,FALSE)The output in cell E22 is FALSE as the quantity 33 doesn’t fall in the range.
After that, we put the whole dataset as the List range and cells E21:E22 as the Criteria range.
Finally, hit OK to see the result i.e., a list of products having quantity in the range from 50 to 100.
Read More: Excel Advanced Filter Not Working (2 Reasons & Solutions)
Notes
The Advanced command has some key differences with the Filter function in Excel. It uses separate source range and criteria range to filter data. With this feature, we can use functions and formulas to set criteria as we did in Example 5. In addition, it offers us the option to filter the unique data from a source list easily based on single or multiple criteria.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to filter a column based on multiple criteria using advanced filtering in Excel with 5 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- Excel Advanced Filter (5 Useful Applications)
- How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)
- VBA to Copy Data to Another Sheet with Advanced Filter in Excel
- How to Use Advanced Filter to Copy Data to Another Sheet in Excel
- Use the Advanced Filter in VBA (A Step-by-Step Guideline)
- How to Create Dynamic Advanced Filter in Excel (2 Applications)
- Excel VBA Examples: Use Advanced Filter with Criteria (6 Criteria)
