Advanced Filter in Excel

Advanced Filter is a powerful Excel tool used to extract specific records from a large dataset based on one or more conditions. It provides more control than the normal Filter option.

What Can Advanced Filter Do?

Sample Student Data

Understanding Criteria Range

Advanced Filter requires a Criteria Range. The criteria range contains the column name and the condition to be applied.

Example: Show Only Excel Students

Here, Course must be exactly the same as the column heading in the original table.

Steps to Apply Advanced Filter

1. Create your data table with headings.
2. Create a criteria range anywhere on the worksheet.
3. Click any cell inside the data table.
4. Go to Data Tab.
5. Click Advanced in the Sort & Filter group.
6. The Advanced Filter dialog box appears.
7. Select the List Range (your original table).
8. Select the Criteria Range.
9. Choose whether to:
   • Filter the list in place
   • Copy to another location
10. Click OK.

Example 1: Single Condition

Display only students enrolled in Excel.

Criteria Range

Result

Example 2: AND Condition

Show students whose Course is Excel AND Fees are greater than 5500.

Criteria Range

Conditions written on the same row mean AND.

Result: Amit only.

Example 3: OR Condition

Show students enrolled in Excel OR Python.

Criteria Range

Conditions written on different rows mean OR.

Copy Filtered Data to Another Location

1. Open Advanced Filter.
2. Select Copy to another location.
3. Select the output cell (example: H1).
4. Click OK.
5. Excel copies only the filtered records.

Extract Unique Records

Advanced Filter can remove duplicate values and display only unique records.

Steps

1. Open Advanced Filter.
2. Select your List Range.
3. Check Unique Records Only.
4. Click OK.

Excel displays each value only once.

Common Mistakes

Shortcut Key