Advanced filtering criteria in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of using advanced filtering criteria in Excel?

Advanced filtering criteria help you extract specific data from a large dataset by applying complex conditions beyond simple filters, such as multiple conditions combined with AND/OR logic.

beginner

How do you set up criteria for an Advanced Filter in Excel?

You create a criteria range on the worksheet with the same column headers as your data, then enter the conditions below those headers. This range tells Excel what to filter.

intermediate

Explain how to use OR logic in advanced filtering criteria.

To apply OR logic, list multiple criteria in separate rows under the same column header in the criteria range. Excel will include rows that meet any of these conditions.

intermediate

How do you apply AND logic in advanced filtering criteria?

Place multiple conditions in the same row but under different column headers in the criteria range. Excel filters rows that meet all these conditions simultaneously.

beginner

What is a common mistake to avoid when setting up advanced filter criteria?

A common mistake is not matching the column headers exactly between the data and the criteria range, which causes the filter to fail or return incorrect results.

What must the criteria range include for an Advanced Filter to work correctly?

A Only one column header regardless of data

B Only the conditions without headers

C Random text to describe the filter

D Column headers matching the data and conditions below

The criteria range must have the same column headers as the data, with conditions listed below to tell Excel what to filter.

How do you represent OR conditions in the criteria range?

A Multiple conditions in different rows

B Multiple conditions in different columns

C Multiple conditions in the same row

D Using commas inside one cell

OR conditions are placed in separate rows under the same column header, so Excel includes rows matching any condition.

If you want to filter rows where 'City' is 'New York' AND 'Sales' are greater than 1000, how do you set the criteria?

A Put both conditions in the same row under 'City' and 'Sales' headers

B Put conditions in different rows under 'City' header

C Put conditions in different columns but different rows

D Use only one condition at a time

AND logic requires conditions in the same row but under different column headers.

What happens if the column headers in the criteria range do not exactly match the data headers?

A Excel ignores the criteria and filters all data

B Excel does not filter correctly or returns no results

C Excel returns an error message

D Excel automatically corrects the headers

Headers must match exactly; otherwise, Excel cannot apply the criteria properly.

Which of the following is NOT a valid way to use advanced filter criteria?

A Using wildcards like * and ? in criteria

B Using formulas in the criteria range

C Using commas to separate multiple conditions in one cell

D Using multiple rows for OR conditions

Commas inside one cell do not separate conditions; separate rows or columns are needed.

Describe how to set up and use an advanced filter with both AND and OR conditions in Excel.

Think about how rows and columns in the criteria range represent AND and OR.

Explain common mistakes to avoid when creating advanced filtering criteria and how to fix them.

Focus on headers and condition layout.