Apply Advanced Filter Based on Multiple Criteria in One Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Advanced Filter Based on Multiple Criteria in One 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”.

Excel Advanced Filter Based on Multiple Criteria in One Column

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.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • 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.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • 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.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • We’ve successfully filtered the dataset for products of categories Bars or Snacks.

Excel Advanced Filter Based on Multiple Criteria in One Column

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.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • 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.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • Now select the helper column.
  • Go to the Data tab from the Excel Ribbon.
  • Click on the Filter option.

Excel Advanced Filter Based on Multiple Criteria in One Column

  • 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.

Excel Advanced Filter Based on Multiple Criteria in One Column

Read More: Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)


Similar Readings


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.

Excel Advanced Filter Based on Multiple Criteria in One Column

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”.

Excel Advanced Filter Based on Multiple Criteria in One Column

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.

Excel Advanced Filter Based on Multiple Criteria in One Column

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”.

Excel Advanced Filter Based on Multiple Criteria in One Column

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.

Excel Advanced Filter Based on Multiple Criteria in One Column

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

Al Arafat Siddique
Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo