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.

Custom Autofilter with More Than 2 Criteria in GIF

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.

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 to or less than $1000.

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

Showing Custom Autofilter Arrow for Sales

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

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

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 results based on more than 2 criteria.

Result Output with 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.

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 results 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

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.

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 Sales 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 Sales 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 Arrowhead in the Criteria range.

Setting Criteria in Advanced Filter

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

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.

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.

Assigning Data into Filter

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.

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

Practice Section

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

Showing Practice Section in Workbook

Download Practice Workbook

You can download and practice this workbook.


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!
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

  1. 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?

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

      Yousuf Khan Shovon

Leave a reply

Advanced Excel Exercises with Solutions PDF