How to Use Advanced Filters?

Go to the Data ribbon and click on Advanced Filters in the Sort & Filter group.

Enter data in the dialog box that appears.

List Range: This is the data table where you want to apply filters

Criteria Range: This is where you define the criteria on basis of which the data will be filtered. In this example, we are filtering data of all the employees who are from the HR department

Copy to: Cell where the filtered data will be pasted

Once you enter these details after selecting the option to Copy to another location; press OK. The output is copied to F1 as shown below:

Advanced Filters with AND Condition

There are times where there is a need to filter data satisfying multiple conditions. Advanced filters help you in achieving this easily.

In the given example if we want to find out the list of employees who are in the Production department AND have the designation as Manager. This AND condition can be written as below:

The Criteria range in the Advanced Filter dialog box should include the necessary rows, as illustrated below. The table containing the employee data is in the range A5 to D15.

Advanced Filters with Formulas

We have looked at how Advanced Filters work. We have also explored how to refine the filter criteria using the AND, OR operator. However, sometimes the conditions are such that you need to specify conditions beyond AND & OR.

In such cases, you could filter basis a formula. In the example if we want to filter for the employees who are above 40 years of age, then we can provide the formula for age as illustrated below:

The Criteria range in the Advanced Filter dialog box should include the necessary rows, as illustrated below. The table containing the employee data is in the range A5 to D15.