FILTER function in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the FILTER function do in Google Sheets?

The FILTER function returns only the rows or columns from a range that meet a specific condition or set of conditions.

beginner

Syntax of the FILTER function?

FILTER(range, condition1, [condition2, ...])

Where range is the data to filter, and condition1, condition2, etc. are logical tests that decide which rows or columns to keep.

beginner

How to filter rows where column A is greater than 10?

Use =FILTER(A1:B10, A1:A10 > 10).
This returns rows from A1:B10 where values in column A are more than 10.

intermediate

What happens if no rows meet the FILTER condition?

FILTER returns a #N/A error saying "No matches are found in FILTER evaluation." You can handle this with IFERROR to show a friendly message.

intermediate

Can FILTER use multiple conditions? How?

Yes! You can add multiple conditions separated by commas, which act like AND.
Example: =FILTER(A1:C10, A1:A10>5, B1:B10="Yes") filters rows where column A is >5 AND column B equals "Yes".

What does the FILTER function return?

A Only the data rows that meet the condition

B All data regardless of condition

C Only the first row of data

D A sum of all values

FILTER returns only rows or columns that satisfy the given condition(s).

Which is the correct syntax for FILTER?

A FILTER(range)

B FILTER(range, condition1, condition2, ...)

C FILTER(condition1, range)

D FILTER(range, condition1 + condition2)

FILTER requires the range first, then one or more conditions separated by commas.

How do you filter rows where column B equals "Yes"?

A =FILTER(A1:C10, B1:B10="Yes")

B =FILTER(A1:C10, B1:B10>"Yes")

C =FILTER(A1:C10, B1:B10<"Yes")

D =FILTER(A1:C10, B1:B10<>"Yes")

Use the equality condition B1:B10="Yes" to filter rows where column B is exactly "Yes".

What does FILTER return if no data matches the condition?

A The entire range

B An empty cell

C Zero

D #N/A error

FILTER returns a #N/A error with message "No matches are found in FILTER evaluation."

How to apply multiple conditions in FILTER?

A Use semicolons between conditions

B Add conditions with plus signs (+)

C Separate conditions by commas (AND logic)

D Put all conditions inside one string

Multiple conditions are separated by commas and act like AND, meaning all must be true.

Explain how to use the FILTER function to show only rows where sales are above 100.

Think about the range of data and the condition that checks sales values.

Describe what happens when FILTER finds no matching data and how to handle it.

Consider what the user sees and how to show a friendly message instead.

Practice

1. What does the FILTER function do in Google Sheets?

easy

A. It changes the color of cells.

B. It sorts data alphabetically.

C. It returns only the rows that meet certain conditions.

D. It adds numbers in a range.

Solution

1. Step 1: Recall the purpose of the FILTER function

   The FILTER function is designed to pick and show only the rows that match given rules or conditions. Sorting, adding, or coloring cells are different functions and not what FILTER does.

2. Final Answer:

   It returns only the rows that meet certain conditions. -> Option C

3. Quick Check:

   FILTER picks matching rows = It returns only the rows that meet certain conditions. [OK]

Hint: FILTER shows only rows matching your rules [OK]

Common Mistakes:

• Thinking FILTER sorts data
• Confusing FILTER with SUM or SORT
• Assuming FILTER changes cell colors

2. Which of these is the correct syntax for the FILTER function?

easy

A. =FILTER(range, condition1 AND condition2)

B. =FILTER(condition1, condition2, range)

C. =FILTER(range; condition1; condition2)

D. =FILTER(range, condition1, condition2, ...)

Solution

1. Step 1: Verify the correct FILTER syntax

   =FILTER(range, condition1, condition2, ...) has range first, then conditions (A). B wrong order, C semicolons invalid, D AND inside incorrect.

2. Final Answer:

   =FILTER(range, condition1, condition2, ...) -> Option D

3. Quick Check:

   FILTER syntax = range first, then conditions [OK]

Hint: FILTER always starts with range, then conditions [OK]

Common Mistakes:

• Putting conditions before range
• Using semicolons instead of commas
• Trying to combine conditions with AND inside FILTER

3. Given this data in A1:B5:

A1: Name  B1: Score
A2: Amy   B2: 85
A3: Bob   B3: 90
A4: Cara  B4: 75
A5: Dan   B5: 95

What will =FILTER(A2:A5, B2:B5 > 80) return?

medium

A. ["Amy", "Cara", "Dan"]

B. ["Amy", "Bob", "Dan"]

C. ["Bob", "Dan"]

D. ["Cara"]

Solution

1. Step 1: Evaluate the FILTER formula on the data

   Filters A2:A5 where B2:B5 > 80: Amy(85) yes, Bob(90) yes, Cara(75) no, Dan(95) yes. Returns ["Amy", "Bob", "Dan"].

2. Final Answer:

   ["Amy", "Bob", "Dan"] -> Option B

3. Quick Check:

   FILTER names with scores > 80 = Amy, Bob, Dan [OK]

Hint: Match condition to filter rows, then list matching names [OK]

Common Mistakes:

• Including Cara who scored 75
• Only listing Bob and Dan, missing Amy
• Confusing ranges and conditions

4. This formula gives an error: =FILTER(A2:A5, B2:B5 > 80, C2:C5 = "Yes")
Given columns A and B have data but C is empty. What is the fix?

medium

A. Remove the condition on C2:C5 from the formula.

B. Fill column C with values before using FILTER.

C. Change the formula to =FILTER(A2:A5, B2:B5 > 80) and add IFERROR.

D. Use =FILTER(A2:A5, B2:B5 > 80, ISBLANK(C2:C5)) instead.

Solution

1. Step 1: Identify and fix the FILTER error

   Column C empty makes C2:C5="Yes" always FALSE, no matches, #N/A error. Fix: remove C2:C5 condition.

2. Final Answer:

   Remove the condition on C2:C5 from the formula. -> Option A

3. Quick Check:

   Remove empty column condition to fix FILTER error [OK]

Hint: Remove conditions on empty columns to avoid errors [OK]

Common Mistakes:

• Trying to fix with IFERROR without fixing condition
• Filling column C unnecessarily
• Using ISBLANK incorrectly in FILTER

5. You have a sales table with columns: Product (A2:A10), Region (B2:B10), and Sales (C2:C10).

You want to list all products sold in the "East" region with sales over 1000. Which formula correctly does this?

hard

A. =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000)

B. =FILTER(A2:A10, B2:B10 = "East" AND C2:C10 > 1000)

C. =FILTER(A2:A10, (B2:B10 = "East") + (C2:C10 > 1000))

D. =FILTER(A2:A10, OR(B2:B10 = "East", C2:C10 > 1000))

Solution

1. Step 1: Identify the correct FILTER formula for AND conditions

   Commas separate conditions (AND logic): =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) (B). A AND invalid, C + incorrect, D OR wrong.

2. Final Answer:

   =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) -> Option A

3. Quick Check:

   Multiple conditions in FILTER use commas = =FILTER(A2:A10, B2:B10 = "East", C2:C10 > 1000) [OK]

Hint: Use commas for AND conditions in FILTER [OK]

Common Mistakes:

• Using AND or OR inside FILTER incorrectly
• Using + to combine conditions
• Confusing AND with OR logic