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

Get FREE Advanced Excel Exercises with Solutions!

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.

Custom Autofilter with More Than 2 Criteria in GIF


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.

Showing Sample Dataset

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

Steps for Custom Autofilter with More Than 2 Criteria

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.

Showing Custom Autofilter Arrow for Sales

Showing Custom Autofilter 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.

Assigning 2 Criteria in Custom Autofilter

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.

Showing Result with 2 Criteria

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

Steps for Custom Autofilter

  • Then, Select equals from the dropdown box and Write iPhone 14 in the adjacent box.
  • And, Press OK.

Setting Criteria in Custom Autofilter

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

Result Output with More Than 2 Criteria

Read More: How to Use Auto Filter and Advanced Filter in Excel


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.

Dataset to Custom Autofilter with More Than 2 Criteria

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 result based on three criteria.

Inserting FILTER Formula to Custom Autofilter with More Than 2 Criteria

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

FILTER Formula Result

Read More: Filling a Certain Number of Rows in Excel Automatically (6 Methods)


Similar Readings


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.

Dataset for Advanced Autofilter

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.

Adding Criteria Table for Custom Autofilter

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

  • So, Write the names of Sale Reporters in the criteria table.

Setting Criteria in 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.

Using Advanced Filter for More Than 2 Criteria

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.

Setting Criteria in Advanced Filter

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

Setting Criteria in Criteria range

It will take you to the previous Advanced Filter window.

  • Now, Click on OK.

Setting Criteria in Advanced Filter

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

Showing Result for Custom Autofilter with More Than 2 Criteria

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

Read More: Remove Advanced Filter in Excel (5 Effective Ways)


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.

Assigning Data into Filter

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.

Showing Filter Arrow

  • Then, Write the product name ( ipad ) in the Text Product box and Press OK.

Setting Single Criteria for Custom Autofilter

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

Showing Result for Custom Autofilter with Single Criteria

Read More: How to Create a Custom AutoFill List in Excel (2 Quick Methods)


Practice Section

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

Showing Practice Section in Workbook


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.


Related Articles

Sourav Kundu
Sourav Kundu

Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo