FILTER function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to filter values in column A that are greater than 10.

Google Sheets

=FILTER(A2:A10, A2:A10 [1] 10)

Drag options to blanks, or click blank then click option'

A>

B<=

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will filter values less than 10.

Using '=' will only filter values exactly equal to 10.

The FILTER function returns values from A2:A10 where the condition is true. Here, we want values greater than 10, so we use >.

2fill in blank

medium

Complete the formula to filter values in column B that are not empty.

Google Sheets

=FILTER(B2:B20, B2:B20 [1] "")

Drag options to blanks, or click blank then click option'

A<>

B=

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' will filter only empty cells.

Using '>' or '<' does not correctly check for non-empty text.

The operator <> means 'not equal to'. This filters cells in B2:B20 that are not empty strings.

3fill in blank

hard

Fix the error in the formula to filter values in column C that are less than or equal to 50.

Google Sheets

=FILTER(C2:C15, C2:C15 [1] 50)

Drag options to blanks, or click blank then click option'

A=>

B<=

C<

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=>' which is not valid in Google Sheets formulas.

Using '==' which is not a valid comparison operator here.

The correct operator for 'less than or equal to' is <=. The option '=>' is invalid syntax.

4fill in blank

hard

Fill both blanks to filter values in column D that are between 20 and 40 inclusive.

Google Sheets

=FILTER(D2:D30, D2:D30 [1] 20, D2:D30 [2] 40)

Drag options to blanks, or click blank then click option'

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '<=' excludes 40 from results.

Using '>' instead of '>=' excludes 20 from results.

The formula filters values greater than or equal to 20 and less than or equal to 40. So use >= for the lower bound and <= for the upper bound.

5fill in blank

hard

Fill all three blanks to filter values in column E where values are greater than 5 and less than 15, and the corresponding value in column F is 'Yes'.

Google Sheets

=FILTER(E2:E25, E2:E25 [1] 5, E2:E25 [2] 15, F2:F25 [3] "Yes")

Drag options to blanks, or click blank then click option'

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using >= or <= instead of > or < changes the range.

Using <> instead of = for matching 'Yes' will filter out those rows.

The formula filters values in E2:E25 greater than 5 and less than 15, and only rows where F2:F25 equals 'Yes'. So use > for greater than, < for less than, and = for equality.

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