Explanation

Advanced Filter is useful when filtering data using a separate criteria range or when results need to be copied elsewhere.

Dataset

Employee	Department	City	Sales
Amit	Sales	Sirsa	85000
Neha	HR	Hisar	45000
Rahul	Sales	Sirsa	120000
Priya	Finance	Fatehabad	95000
Simran	Sales	Hisar	70000

Criteria Range

Department	Sales
Sales	>80000

This represents:

Department = Sales AND Sales > ₹80,000

Matching records:

Employee	Department	City	Sales
Amit	Sales	Sirsa	85000
Rahul	Sales	Sirsa	120000

Filter the List, In-Place

This hides records that do not satisfy the criteria while keeping the filtered results in the original location.

Copy to Another Location

Advanced Filter can also copy matching records to another area of the worksheet.

This is useful for creating separate reports from a master dataset.

Basic Steps

1. Create the dataset.
2. Create a criteria range.
3. Go to Data → Advanced.
4. Select List Range.
5. Select Criteria Range.
6. Choose Filter in Place or Copy to Another Location.
7.  

Practice

Use Advanced Filter to extract:

• Sales department employees
• Sales above ₹80,000
• Employees from Sirsa

Copy one filtered result to another worksheet area.