Advanced Filter Options & Extracting Unique Records0%

Creating and Formatting Excel Tables (Ctrl + T)

Advanced Filter Options & Extracting Unique Records

Beginner12 min read•Updated: Jul 11, 2026

While standard AutoFilter handles straightforward criteria, Excel's Advanced Filter handles complex boolean logic (combining multiple AND/OR statements across various columns) and allows you to copy filtered records or extract a clean list of unique values directly to a separate location.

1. Setting Up Criteria Ranges for Advanced Filter

Advanced Filter requires three dedicated ranges on your sheet:

1
List Range: Your primary raw data table including headers (e.g., 'A1:E200').
2
Criteria Range: A separate block containing identical column headers with search conditions placed below them.
3
Copy To Range (Optional): Destination headers where extracted rows should be pasted.

1Criteria Range Setup Logic:
2- Conditions placed on the SAME ROW evaluate as AND logic.
3- Conditions placed on DIFFERENT ROWS evaluate as OR logic.
4 
5Example (AND Logic):
6+-------------------+------------------+
7| Department        | Salary           |
8+-------------------+------------------+
9| Sales             | >50000           |  <- (Department = "Sales" AND Salary > 50000)
10+-------------------+------------------+
11 
12Example (OR Logic):
13+-------------------+------------------+
14| Department        | Salary           |
15+-------------------+------------------+
16| Sales             |                  |  <- (Department = "Sales")
17| IT                | >70000           |  <- OR (Department = "IT" AND Salary > 70000)
18+-------------------+------------------+

2. Step-by-Step: Executing Advanced Filter

1
Set up your criteria range in empty cells above or to the side of your main table.
2
Go to Data > Advanced (in the Sort & Filter group).
3
In the Advanced Filter dialog box:

Select Copy to another location (recommended so original data is untouched).
List range: Select '$A$1:$E$200'.
Criteria range: Select '$G$1:$H$2'.
Copy to: Select destination cell '$J$1'.

1
Click OK. Excel extracts all matching records immediately to Column J!

3. Extracting Unique (Distinct) Records

Removing duplicates directly alters your source data. If you want to generate a unique list of clients, products, or cities without touching the original table:

1
Click Data > Advanced.
2
Select Copy to another location.
3
In List range, select the column containing duplicate entries (e.g., '$B$1:$B$500').
4
Leave Criteria range completely blank.
5
In Copy to, select an empty cell (e.g., '$M$1').
6
Check the box Unique records only.
7
Click OK.

Excel outputs a deduplicated, clean list of unique values!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In Excel Advanced Filter criteria ranges, how do you specify an OR condition between two fields?

Question 2

What happens if you check 'Unique records only' in the Advanced Filter dialog box?

Question 3

Where must the column headers in an Advanced Filter Criteria Range come from?

Question 4

Which Advanced Filter action setting keeps your original data in place while writing the filtered subset into a new location?

Question 5

If your criteria range specifies Department as 'Finance' and Rating as '>4' on the exact same row, what records will be returned?

Next Lesson

Creating and Formatting Excel Tables (Ctrl + T)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Data with AutoFilter	Creating and Formatting Excel Tables (Ctrl + T)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Creating and Formatting Excel Tables (Ctrl + T)

Advanced Filter Options & Extracting Unique Records

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

1. Setting Up Criteria Ranges for Advanced Filter

Advanced Filter requires three dedicated ranges on your sheet:

1
List Range: Your primary raw data table including headers (e.g., 'A1:E200').
2
Criteria Range: A separate block containing identical column headers with search conditions placed below them.
3
Copy To Range (Optional): Destination headers where extracted rows should be pasted.

1Criteria Range Setup Logic:
2- Conditions placed on the SAME ROW evaluate as AND logic.
3- Conditions placed on DIFFERENT ROWS evaluate as OR logic.
4 
5Example (AND Logic):
6+-------------------+------------------+
7| Department        | Salary           |
8+-------------------+------------------+
9| Sales             | >50000           |  <- (Department = "Sales" AND Salary > 50000)
10+-------------------+------------------+
11 
12Example (OR Logic):
13+-------------------+------------------+
14| Department        | Salary           |
15+-------------------+------------------+
16| Sales             |                  |  <- (Department = "Sales")
17| IT                | >70000           |  <- OR (Department = "IT" AND Salary > 70000)
18+-------------------+------------------+

2. Step-by-Step: Executing Advanced Filter

1
Set up your criteria range in empty cells above or to the side of your main table.
2
Go to Data > Advanced (in the Sort & Filter group).
3
In the Advanced Filter dialog box:

Select Copy to another location (recommended so original data is untouched).
List range: Select '$A$1:$E$200'.
Criteria range: Select '$G$1:$H$2'.
Copy to: Select destination cell '$J$1'.

1
Click OK. Excel extracts all matching records immediately to Column J!

3. Extracting Unique (Distinct) Records

Removing duplicates directly alters your source data. If you want to generate a unique list of clients, products, or cities without touching the original table:

1
Click Data > Advanced.
2
Select Copy to another location.
3
In List range, select the column containing duplicate entries (e.g., '$B$1:$B$500').
4
Leave Criteria range completely blank.
5
In Copy to, select an empty cell (e.g., '$M$1').
6
Check the box Unique records only.
7
Click OK.

Excel outputs a deduplicated, clean list of unique values!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In Excel Advanced Filter criteria ranges, how do you specify an OR condition between two fields?

Question 2

What happens if you check 'Unique records only' in the Advanced Filter dialog box?

Question 3

Where must the column headers in an Advanced Filter Criteria Range come from?

Question 4

Which Advanced Filter action setting keeps your original data in place while writing the filtered subset into a new location?

Question 5

If your criteria range specifies Department as 'Finance' and Rating as '>4' on the exact same row, what records will be returned?

Next Lesson

Creating and Formatting Excel Tables (Ctrl + T)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Data with AutoFilter	Creating and Formatting Excel Tables (Ctrl + T)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next