We know that we can set filters on columns and filter data with one click in Microsoft Excel.This can be done by clicking “Filter” on the “Data” tab. Apart from simply filtering data based on columns, we can even completely filter a dataset based on some conditions. Suppose we want to filter a dataset that meets certain criteria, then this can be done using Advance Filter. In this article, I will guide you on how to use the Advanced Filter in Excel .
Using the advanced filter in Microsoft Excel
Let me explain this with an example of these employees. I have a dataset that contains data for hours worked, education in years, annual income, field, and state. Now I want to filter the data that matches the condition like this:
Condition for a man:
- Normal working hours: 40
- Education: 13
- Annual income: over 35,000
- Gender: male
- State: Alaska
Condition for a woman:
- Regular schedule hours: above 35
- Education: more than 12
- Annual income: over 12,000
- Gender 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 inside it there was an AND condition that had to be met.
Now, in order to filter out data that meets these conditions, we need to process the Excel sheet a little differently. This is where the prefilter in Excel comes into picture. We will now prepare the criteria in the same Excel sheet. Create two rows above the original dataset with the same column names as the existing data, as shown below from A3 to E5.
Now we will populate the criteria columns with the data specified in the conditions. As mentioned in the man’s condition, fill in the entire row with the indicated columns. So line 4 will be filled in as shown below.
Repeat the same for the next line 5 based on the condition for the woman as shown below. Now to make it clear if the data in each row is OR- related , and the data in those rows (column data) is AND- related . So, we have just created rows that meet the criteria by which we need to filter the data.
Now it’s time to use a prefilter in Excel to filter your data. First, click any cell in the original dataset, go to the Data tab, and click the Advanced button. This will automatically fill in the list range. Click on the small button next to the criteria range . Now select the criteria range, i.e. A3 to E5 and press the same little button to set the criteria range. Now click “OK”. This will filter the data the way you want it to meet the criteria.
