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