FILTER function in Google Sheets - Step-by-Step Guide

Introduction

The FILTER function helps you pick out only the rows or columns that meet certain rules from a bigger list. It saves time by showing just the data you want without changing the original list.

When you want to see only sales records from a specific city in a big sales list.

When you need to list all employees who joined after a certain date from a staff database.

When you want to extract all products with prices above a set amount from your inventory.

When you want to create a smaller list of students who scored above 80 in a test.

When you want to show only the orders that are marked as 'Delivered' from your orders sheet.

Steps

Step 1: Click

- a blank cell where you want the filtered results to appear

The cell is selected and ready for input

Step 2: Type

- the selected cell

The formula input starts

💡 Start typing =FILTER to see the function suggestion

Step 3: Enter

- the formula bar

The FILTER formula syntax appears: =FILTER(range, condition1, [condition2, ...])

Step 4: Replace

- range in the formula

The range of cells you want to filter is set

💡 For example, A2:C20 to filter rows from columns A to C

Step 5: Replace

- condition(s) in the formula

The rule(s) to filter by are set

💡 For example, B2:B20="New York" to filter rows where column B is New York

Step 6: Press

- Enter key

The filtered list appears starting from the selected cell

Before vs After

Before

A list of 20 sales records with columns for Date, City, and Amount

After

Only the rows where City is 'New York' appear, showing Date, City, and Amount for those rows

Settings Reference

range

📍 inside the FILTER formula

Defines the data area to filter

Default: none

condition

📍 inside the FILTER formula after the range

Sets the rule(s) that rows must meet to be included

Default: none

Common Mistakes

Using a condition range that does not match the size of the data range

FILTER requires the condition range to be the same size as the data range to work properly

Make sure the condition range covers the same number of rows as the data range

Not enclosing text conditions in quotes

Text values must be inside double quotes to be recognized correctly

Write conditions like B2:B20="New York" with quotes around text

Forgetting to press Enter after typing the formula

The formula won't run until you press Enter

Always press Enter to apply the FILTER formula and see results

Summary

FILTER lets you show only rows that meet your rules from a bigger list.

You write =FILTER(range, condition) to use it.

Make sure your condition range matches your data range size exactly.

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