FILTER function in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table shows names and ages of four people.

Cell	Value
A1	Name
B1	Age
A2	Alice
B2	30
A3	Bob
B3	25
A4	Charlie
B4	35
A5	Diana
B5	28

Formula Trace

=FILTER(A2:A5, B2:B5>28)

Step 1: B2:B5>28

Step 2: FILTER(A2:A5, [TRUE, FALSE, TRUE, FALSE])

Cell Reference Map

    A       B
1 |Name   | Age  |
2 |Alice  | 30   |
3 |Bob    | 25   |
4 |Charlie| 35   |
5 |Diana  | 28   |

Formula references A2:A5 and B2:B5 for filtering.

The formula uses the names in A2:A5 and checks ages in B2:B5 to filter names with age > 28.

Result

    C
1 |Filtered Names|
2 |Alice         |
3 |Charlie       |

The FILTER formula outputs the names Alice and Charlie in the result column because their ages are greater than 28.

Sheet Trace Quiz - 3 Questions

What does the condition B2:B5>28 return for the ages?

A [TRUE, TRUE, TRUE, TRUE]

B [FALSE, TRUE, FALSE, TRUE]

C [TRUE, FALSE, TRUE, FALSE]

D [FALSE, FALSE, FALSE, FALSE]

Key Result

FILTER(range_to_return, condition_range condition) returns values from range_to_return where condition is TRUE.

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