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.

Download Practice Workbook

You can download and practice this workbook.

Using Custom AutoFilter with More Than 2 Criteria in Excel

There are many ways to autofilter 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 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 criteria. we want to find out all the records of iPhone 14 which have been sold between $500 and $1000.

So, Click on the Arrow head 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 result based on more than 2 criteria.

Using FILTER Function for More Than 2 Criteria

We can use the FILTER function to autofilter 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.

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 autofilter 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 table Sale 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 Sale 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 arrow head in the Criteria range.

• Then, Select the Criteria range as G4:G8 and Click on the downward arrow head.

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 single criteria. We are going to show you how you can auto filter the dataset on single criteria.

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

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. For content like this visit our website ExcelDemy.com.