How to use Advance Filter in Excel

We know that we can set filters on columns and filter the data with just a click in Microsoft Excel. It can be done by clicking on the ‘Filter’ under “Data” tab. Apart from just filtering the data based on columns, we can even filter the data set completely based on some conditions. Suppose, we want to filter the data set matching the certain criteria, then can be done using Advance Filter. In this article I will let you know how to use Advance Filter in Excel.

Use Advance Filter in Excel Total data

Using Advance Filter in Microsoft Excel

Let me explain this with the sample data of Employees. I have the data set containing Usual Hours Worked, Education in Years, Yearly Earnings, Sex and State. Now, I want to filter the data meeting the condition as follows,

Condition for Male:

  • Usual Hours Worked : 40
  • Education : 13
  • Yearly Earnings : Greater than 35000
  • Sex : Male
  • State : Alaska

Condition for Female:

  • Usual Hours Worked : Greater than 35
  • Education : Greater than 12
  • Yearly Earnings : Greater than 12000
  • Sex : Female
  • State : Alaska

If we see the conditions, we need to filter the data of Male and Female employees separately. I mean there was an OR condition and within that there was AND condition to be met.

Now to filter out the data meeting this conditions we need to deal the Excel sheet a bit differently. This is where advance filter in Excel comes in to the picture. Now, we will prepare the criteria in the same Excel sheet itself. Create two rows above the original data set with the same column names as the already present data as shown below from A3 to E5.

Use Advance Filter in Excel columns in criteria region

Now, we will fill the criteria columns with the data as mentioned in the conditions. As mentioned in Condition for Male, fill the entire row with the specified columns. So, Row 4 will be filled as shown below.

Use Advance Filter in Excel added condition in criteria region

Repeat the same for the next Row 5 based on Condition for Female as shown below. Now, to make it clear, if data in each row are linked with OR condition and data within that rows (column wise data) is linked with AND condition. So, we just created the rows meeting the criteria with which we need to filter the data.

Use Advance Filter in Excel added condition 2 in criteria region

Now, it is the time to use advance filter in Excel to filter the data. First click any cell on your original dataset, click “Data” tab and click on “Advanced” button. This will automatically fill the List Range. Click on the small button beside the Criteria range. Now, select the criteria range i.e; A3 to E5 and click on the same small button to set the criteria range. Now, click “Ok”. This will filter the data as you want meeting the criteria.

Use Advance Filter in Excel

Note: Column Names of the criteria range should be exactly the same as the column names of the data set to get this worked.

Advance Filter in Excel allows us to filter the data meeting the complex queries. So, what query or condition you have used to filter the data? Please, let us know through comments if you have anything to add.

Posted by on , in Category Office with Tags
Sridhar is a Software Engineer who loves to stay updated with developments in the world of technology. He is fond of writing everything related to Internet, Computers and Mobile and Desktop Operating Systems.