Advanced Filter

Advanced Filter is a powerful filtering tool that allows you to apply complex criteria and extract filtered data to another location.

It is more flexible than Auto Filter and is useful for advanced data analysis.

Why Use Advanced Filter?

• Apply multiple conditions
• Use complex criteria (AND / OR logic)
• Extract unique records
• Copy filtered data to another location
• Perform dynamic filtering

How to Apply Advanced Filter

Step 1: Prepare Your Data

Make sure:

• Data has proper headers
• No blank rows in dataset

Step 2: Create Criteria Range

Create a small criteria table using the same header names.

Example:

Department	Salary
HR	>50000

Header names must exactly match original table headers.

Step 3: Apply Advanced Filter

1. Select the main data
2. Go to Data tab
3. Click Advanced
4. Choose:
   • Filter the list, in-place
     OR
   • Copy to another location
5. Select:
   • List range
   • Criteria range
6. Click OK

Example 1: AND Condition

Criteria:

Department	Salary
HR	>50000

This returns employees from HR with salary above 50,000.

Example 2: OR Condition

Criteria:

Department
HR
IT

Place conditions in different rows to apply OR logic.

Example 3: Extract Unique Records

1. Click Advanced
2. Check Unique records only
3. Click OK

Excel will remove duplicates from filtered results.

Using Formula in Criteria

You can also use formulas.

Example (Salary greater than average):

=B2>AVERAGE($B$2:$B$10)

Formula must return TRUE or FALSE.

Important Tips

• Criteria headers must match exactly
• Avoid blank rows
• Use copy to another location for reports
• Advanced Filter does not update automatically

Difference Between Auto Filter and Advanced Filter

Auto Filter	Advanced Filter
Simple filtering	Complex filtering
Quick dropdown	Criteria-based
Cannot copy results	Can copy results
Limited conditions	Unlimited conditions

---

Why Advanced Filter is Important

Advanced Filter is essential for complex reporting and professional data analysis. It provides more control and flexibility compared to basic filtering tools.

It is highly useful in HR, finance, sales, and data management tasks.