How to Use Custom Autofilter in Excel for More Than 2 Criteria

Excel spreadsheet can be used for Custom Autofilter for more than 2 criteria. This filter option is particularly useful for sorting a dataset. In this article, we will show you how to use this Autofilter tool to sort data with more than 2 criteria.

How to Use Custom AutoFilter with More Than 2 Criteria in Excel

There are many ways to auto-filter a dataset in an Excel worksheet. We have to set different criteria for this auto-filtering process. So, let’s begin.

We have taken this sample dataset of Sales Information of an Apple outlet. In this dataset, we have included the ID Number, Sales Reporters, Product, and Sales Column.

We can use the Custom Autofilter to filter data on more than two criteria. So, let’s see the procedure.

We want to filter records of iPhone 14 that have been sold at equal or greater than $500 and also are equal to or less than $1000.

First, click on the arrow sign in the Sales heading.

Then, from the Number Filters Select the Custom Filter option.

So, this Custom Autofilter window will appear.

There are two criteria rows.

On the first one, select is greater or equal to in the dropdown box and set 500 in the adjacent box.
And, on the second one, Select is less or equal to in the dropdown box and set 1000 in the adjacent box.
Then, press OK.

Thus, the Sales that are between $500 and $1000 will be filtered and displayed in the Excel sheet.

Now, we will set another criterion. we want to find out all the records of iPhone 14 which have been sold between $500 and $1000.

So, click on the Arrowhead in the Product column.

From the appearing window, Go to the Text Filter option and Select Custom Filter.

Then, select equals from the dropdown box and write iPhone 14 in the adjacent box.
And, press OK.

Thus, we get results based on more than 2 criteria.

Using FILTER Function for More Than 2 Criteria

We can use the FILTER function to auto-filter with more than 2 criteria.

For that, we have taken a dataset of sales reports of an Apple outlet.

We want to find out Sales by Watson with more than 2 criteria.

So, add another table like the image below.

Now, write the following formula in G5:J6 and Press ENTER.

=FILTER(B5:E15,(C5:C15="Watson")*(D5:D15="iPhone 14")*(B5:B15="S0002"))

This formula returns results based on three criteria.

Thereby, the range G5:J6 will show the result based on those three criteria.

Advanced Autofilter in Excel Using More Than 2 Criteria

In this process, we will create a data table. Then we have to set a criteria table. Then, we will use the Advanced Filter tool to auto-filter the data table with more than 2 criteria.

First, we have to create a data table. We have taken a data table containing information about the Sales report of an outlet of Apple.

This auto filter requires a criteria table.

We will set four criteria. That’s why we have added the single column Sales Rep. with 4 blank cells. You can set the criteria table as you need.

We want to filter the sale report of 4 Sale Reporters.

So, write the names of Sales Reporters in the criteria table.

Now, copy the data table B4:E15.
Then, go to the Data ribbon and Select the Advanced filter option from the Sort & Filter section.

Doing so, this Advanced Filter window will appear. You can see the List range is already selected.

Now we have to set the criteria.
So, click on the Arrowhead in the Criteria range.

Then, select the Criteria range as G4:G8 and click on the downward Arrowhead.

It will take you to the previous Advanced Filter window.

Now, click on OK.

Therefore, all the Sales records of those 4 sellers will be filtered out.

N.B: You can ignore the change in the criteria table.

Custom Autofilter in Excel Using Single Criteria

You can auto-filter the data set on a single criterion. We are going to show you how you can auto-filter the dataset on a single criterion.

First, select your dataset.
Then, go to the Data bar and select the Filter option from the Sort & Filter section.

Doing so, the Arrow sign will appear on table headings.

We are going to set Product as the criteria. So, click on the arrow in the Product heading.

Then, write the product name (ipad) in the Text Product box and press OK.

Thus, records with the iPad will be filtered out and will appear in the spreadsheet.

Practice Section

You can download the workbook in which we have provided a practice section for you.

Download Practice Workbook

You can download and practice this workbook.

Excel Custom Autofilter More Than 2.xlsx

Conclusion

So, we have shown you how to use Custom Autofilter with more than 2 criteria and also have shown the single and 2 criteria filtering process. Thank you for making it this far. We hope you find the content of this article useful. If there are further queries or suggestions, feel free to mention them in the comment section.

<< Go Back to Excel Auto Filter | Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Ryan Jan 6, 2024 at 11:14 AM

I have a list of warehouse bins that range from 099A to 15000F. When I use the Custom AutoFilter to only show bins that are in the 2000A range, I use “equals 20??A”

Problem is, there are locations that are named 200AA, 200BA, and 200CA. I need a way to filter the double letter bins out from this list without using a filter function that pulls the results into new cells. Something like “equals 20??A” and “does not equal *AA; *BA; *CA”

You know of a way to do this?
- Reply
  Yousuf Khan Jan 14, 2024 at 1:18 PM
  
  Hello Ryan,
  
  To filter the double letter bins out from this list without using a filter function, see if using a range of characters works instead. Then, [0-9] should only match numeric characters. Follow the below steps:
  
  1. Open the Custom AutoFilter dialog. (Read the article if necessary)
  2. Enter Bin in Filed and type the below condition in Criteria:
  20??[0-9][!A-Z]A
  
  Here,
  
  – 20?? matches the first four characters (2000 to 2099).
  – [0-9] matches the fifth character that it’s a numeric digit and not a letter.
  – [!A-Z] matches any character that is not a letter from A to Z, effectively excluding double letters in the fifth and sixth positions.
  – A matches the final “A” in the bin code.
  
  As a result, the filter criteria will accurately find bins in the 2000A range that have a single numeric digit followed by a single letter “A” at the end, excluding those with double letters like 200AA, 200BA, and 200CA.
  
  Regards,
  Yousuf Khan Shovon