Advanced filtering criteria in Excel - Step-by-Step Guide

Introduction

Advanced filtering criteria in Excel let you filter data using multiple conditions and complex rules. This helps you find exactly the data you need from large tables by combining conditions with AND, OR, and custom formulas.

When you want to filter a sales list to show only customers from a specific city who bought more than 100 units.

When you need to extract records where the date is in the last month and the status is 'Completed'.

When you want to filter a product list to show items that are either out of stock or have a price above $50.

When you want to filter data using a custom formula, like showing rows where the total is greater than the average.

When you want to copy filtered data to a new location without changing the original table.

Steps

Step 1: Select

- the data table including headers

The table is highlighted and ready for filtering

Step 2: Go to

- Data tab > Sort & Filter group > Advanced

The Advanced Filter dialog box opens

Step 3: Choose

- Advanced Filter dialog box > Action section

You select either 'Filter the list, in-place' or 'Copy to another location' to decide where filtered data appears

Step 4: Set

- Criteria range field

You enter the range where your filter criteria are written, including headers and conditions

Step 5: If copying filtered data, set

- Copy to field

You specify the cell where filtered results will be pasted

Step 6: Click

- OK button

Excel filters the data based on your advanced criteria and shows or copies the filtered rows

Before vs After

Before

A table with 100 rows showing sales data including columns for City, Units Sold, and Status

After

Only rows where City is 'New York' and Units Sold is greater than 100 remain visible or are copied to a new location

Settings Reference

Action

📍 Advanced Filter dialog box > Action section

Choose whether to filter data in the original table or copy filtered data elsewhere

Default: Filter the list, in-place

List range

📍 Advanced Filter dialog box > List range field

Defines the data table to filter

Default: Automatically set to selected table

Criteria range

📍 Advanced Filter dialog box > Criteria range field

Defines the filter conditions using headers and criteria

Default: Empty

Copy to

📍 Advanced Filter dialog box > Copy to field

Specifies where to paste filtered data when copying

Default: Empty

Common Mistakes

Not including headers in the criteria range

Excel uses headers to match criteria to columns; missing headers cause the filter to fail

Always include the exact column headers in the criteria range above your conditions

Using criteria range that overlaps with the data table

Overlapping ranges cause errors or unexpected results

Place the criteria range in a separate area away from the data table

Entering criteria incorrectly, like mixing AND and OR conditions in the same row

Excel treats criteria in the same row as AND and criteria in different rows as OR; mixing them causes wrong filters

Use one row for AND conditions and multiple rows for OR conditions

Summary

Advanced filtering lets you filter data with multiple complex conditions using a criteria range.

You must include headers in your criteria range that exactly match your data table headers.

Choose to filter data in place or copy filtered results to another location.

Practice

1.

What does having multiple rows in an advanced filter criteria range represent?

easy

A. Each row represents an OR condition between the rows

B. Each row represents an AND condition between the rows

C. Each row is ignored except the first one

D. Each row duplicates the filter results

Solution

1. Step 1: Understand criteria range rows

   In advanced filtering, each row in the criteria range is treated as a separate condition set.

2. Step 2: Identify logical relationship between rows

   Multiple rows mean the filter will include rows matching any one of the criteria rows, which is an OR logic.

3. Final Answer:

   Each row represents an OR condition between the rows -> Option A

4. Quick Check:

   Multiple rows = OR logic [OK]

Hint: Multiple criteria rows mean OR conditions between them [OK]

Common Mistakes:

• Thinking multiple rows mean AND conditions
• Assuming only the first row is used
• Believing rows duplicate results

2.

Which of the following is the correct way to specify a criteria for filtering values greater than 100 in a column named Sales?

easy

A. =Sales>100 under the Sales header

B. >100 under the Sales header in the criteria range

C. Sales>100 in any cell of the criteria range

D. Sales>100 under the Sales header

Solution

1. Step 1: Understand criteria syntax for filtering

   In advanced filter criteria, you write the condition directly under the column header, e.g., >100 for values greater than 100.

2. Step 2: Identify correct syntax for 'greater than'

   The correct way is to put >100 under the Sales header, not including the column name or equals sign.

3. Final Answer:

   >100 under the Sales header in the criteria range -> Option B

4. Quick Check:

   Use operator and value only under header [OK]

Hint: Put only operator and value under header, no column name [OK]

Common Mistakes:

• Including column name in criteria cell
• Using equals sign (=) before condition
• Writing condition outside the criteria range

3.

Given this criteria range for filtering a table with columns Region and Sales:

Region | Sales
East   | >500
West   | <=300

Which rows will be included in the filtered results?

medium

A. Rows where Region is East OR Sales > 500 AND Region is West OR Sales <= 300

B. Rows where Region is East OR Sales > 500 OR Region is West OR Sales <= 300

C. Rows where Region is East AND Sales > 500 OR Region is West AND Sales <= 300

D. Rows where Region is East AND Sales > 500 AND Region is West AND Sales <= 300

Solution

1. Step 1: Analyze each criteria row as AND conditions

   Each row combines conditions with AND, so first row means Region=East AND Sales>500.

2. Step 2: Combine rows with OR logic

   Multiple rows mean OR between them, so second row means Region=West AND Sales<=300.

3. Final Answer:

   Rows where Region is East AND Sales > 500 OR Region is West AND Sales <= 300 -> Option C

4. Quick Check:

   Rows = (East AND >500) OR (West AND <=300) [OK]

Hint: Rows combine AND; multiple rows combine OR [OK]

Common Mistakes:

• Mixing AND and OR incorrectly between rows
• Assuming all conditions apply together
• Ignoring operator precedence in criteria

4.

You set up this criteria range to filter a list with columns Product and Price:

Product | Price
Apple   | >50
Apple   | <=30

But the filter returns no results. What is the likely problem?

medium

A. The criteria rows are combined with OR, so no product can be both >50 and <=30 at once

B. The criteria rows are combined with AND, so no product can be both >50 and <=30 at once

C. The criteria range headers are incorrect

D. The filter only works with numeric columns

Solution

1. Step 1: Understand criteria row logic

   Each row is a separate OR condition, so filter includes rows matching any row.

2. Step 2: Analyze the criteria rows

   Both rows specify Product=Apple but with conflicting Price conditions (>50 OR <=30).

3. Step 3: Why no results?

   If no Apple products have Price >50 or <=30, or data is inconsistent, no rows match.

4. Final Answer:

   The criteria rows are combined with OR, so no product can be both >50 and <=30 at once -> Option A

5. Quick Check:

   Multiple rows = OR, conflicting conditions cause no match [OK]

Hint: Multiple rows mean OR; conflicting conditions may exclude all [OK]

Common Mistakes:

• Thinking rows combine with AND
• Ignoring header names
• Assuming filter only works on numbers

5.

You want to filter a list with columns Category, Quantity, and Status to show rows where:

• Category is "Fruit" and Quantity > 10
• OR Status is "Available" and Quantity <= 5

How should you set up the criteria range?

hard

A. Category | Quantity | Status
Fruit | >10 |
Fruit | <=5 | Available

B. Category | Quantity | Status
Fruit | >10 |
| | Available
| <=5 |

C. Category | Quantity | Status
Fruit | >10 | Available
| <=5 |

D. Category | Quantity | Status
Fruit | >10 |
| <=5 | Available

Solution

1. Step 1: Understand AND and OR in criteria range

   Conditions in the same row combine with AND; multiple rows combine with OR.

2. Step 2: Set first row for Category=Fruit AND Quantity>10

   Put "Fruit" under Category and ">10" under Quantity in first row.

3. Step 3: Set second row for Status=Available AND Quantity<=5

   Put "Available" under Status and "<=5" under Quantity in second row; leave Category blank.

4. Final Answer:

   Category | Quantity | Status Fruit | >10 | | <=5 | Available -> Option D

5. Quick Check:

   Same row = AND; different rows = OR [OK]

Hint: Same row conditions AND; different rows OR [OK]

Common Mistakes:

• Putting all conditions in one row (AND all)
• Misplacing values under wrong headers
• Leaving required cells blank incorrectly