WHERE clause for filtering in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing only the sales records where the sales amount is greater than $500.

📊 Data: You have a sales data table with columns: Date, Salesperson, Region, Product, and Sales Amount.

🎯 Deliverable: Create a filtered table that shows only the rows where Sales Amount is greater than 500.

Sample Data

Date	Salesperson	Region	Product	Sales Amount
2024-05-01	Alice	East	Widget	450
2024-05-02	Bob	West	Gadget	700
2024-05-03	Charlie	East	Widget	520
2024-05-04	Diana	South	Gizmo	300
2024-05-05	Eva	West	Gadget	800
2024-05-06	Frank	East	Gizmo	600
2024-05-07	Grace	South	Widget	400
2024-05-08	Hank	West	Gizmo	550

1

Step 1: Select a cell where you want the filtered table to start, for example, cell G1.

Expected Result

The cell G1 is selected and ready for formula input.

2

Step 2: Enter the FILTER formula to show only rows where Sales Amount is greater than 500. Use the formula referencing the original data range A1:E9.

=FILTER(A1:E9, E1:E9 > 500)

Expected Result

The filtered table shows only rows with Sales Amount greater than 500: rows for Bob, Charlie, Eva, Frank, and Hank.

3

Step 3: Check that the filtered table includes the headers and only the correct rows.

Manually verify the filtered data matches the condition.

Expected Result

Filtered table includes headers and rows with Sales Amount 700, 520, 800, 600, and 550.

Final Result

Date       | Salesperson | Region | Product | Sales Amount
---------------------------------------------------------
2024-05-02 | Bob         | West   | Gadget  | 700
2024-05-03 | Charlie     | East   | Widget  | 520
2024-05-05 | Eva         | West   | Gadget  | 800
2024-05-06 | Frank       | East   | Gizmo   | 600
2024-05-08 | Hank        | West   | Gizmo   | 550

✓Only sales with amounts greater than $500 are shown.

✓This helps focus on higher-value sales for analysis.

✓The FILTER function works like a WHERE clause to filter data.

Bonus Challenge

Modify the filter to show sales greater than $500 only for the East region.

Show Hint

Use FILTER with two conditions combined using multiplication: =FILTER(A1:E9, (E1:E9 > 500) * (C1:C9 = "East"))

Practice

1. What does the WHERE clause do in a Google Sheets QUERY function?

easy

A. It sorts the data alphabetically.

B. It filters rows based on a condition.

C. It adds new columns to the data.

D. It changes the data format.

Solution

1. Step 1: Understand the purpose of WHERE clause

   The WHERE clause is used to select only rows that meet a specific condition.

2. Step 2: Compare options with WHERE clause function

   Sorting, adding columns, or changing format are not done by WHERE; filtering rows is correct.

3. Final Answer:

   It filters rows based on a condition. -> Option B

4. Quick Check:

   WHERE clause = filters rows [OK]

Hint: WHERE filters rows by condition inside QUERY [OK]

Common Mistakes:

• Confusing WHERE with sorting or formatting
• Thinking WHERE adds columns
• Assuming WHERE changes data types

2. Which of the following is the correct syntax to filter rows where column A equals 100 in a QUERY function?

easy

A. SELECT * WHERE A = 100

B. SELECT * WHERE A == 100

C. SELECT * WHERE A equals 100

D. SELECT * WHERE A := 100

Solution

1. Step 1: Recall QUERY syntax for equality

   In QUERY, single equals sign (=) is used to compare values.

2. Step 2: Check each option's syntax

   A uses word 'equals', invalid; B uses '==', invalid; C uses '=', correct; D uses ':=', invalid in QUERY.

3. Final Answer:

   SELECT * WHERE A = 100 -> Option A

4. Quick Check:

   Use '=' for equality in WHERE [OK]

Hint: Use single '=' for equality in QUERY WHERE [OK]

Common Mistakes:

• Using '==' instead of '='
• Writing 'equals' as word
• Using ':=' or other operators

3. Given this QUERY: =QUERY(A1:C5, "SELECT A, B WHERE C > 50") and data:
A=Name, B=Score, C=Age
Rows:
1: John, 80, 45
2: Mary, 90, 55
3: Alex, 70, 60
4: Lisa, 85, 40
What rows will the query return?

medium

A. Mary and Alex

B. No rows

C. All rows

D. John and Lisa

Solution

1. Step 1: Understand the WHERE condition

   The query filters rows where column C (Age) is greater than 50.

2. Step 2: Check each row's Age value

   John (45) and Lisa (40) are not >50; Mary (55) and Alex (60) are >50, so only Mary and Alex are included.

3. Final Answer:

   Mary and Alex -> Option A

4. Quick Check:

   Filter C > 50 returns Mary, Alex [OK]

Hint: Check numeric condition on column values carefully [OK]

Common Mistakes:

• Including rows with C ≤ 50
• Confusing column letters
• Assuming all rows return

4. What is wrong with this QUERY formula? =QUERY(A1:B10, "SELECT A, B WHERE B > 20 AND")

medium

A. SELECT must include C column

B. Incorrect column letters

C. WHERE clause cannot use AND

D. Missing condition after AND

Solution

1. Step 1: Analyze the WHERE clause syntax

   The WHERE clause ends with 'AND' but no condition follows, which is invalid syntax.

2. Step 2: Check other options

   Column letters A and B are valid; AND is allowed but must be followed by a condition; SELECT does not require column C.

3. Final Answer:

   Missing condition after AND -> Option D

4. Quick Check:

   AND must have conditions on both sides [OK]

Hint: Ensure AND has conditions on both sides [OK]

Common Mistakes:

• Leaving AND at end without condition
• Misnaming columns
• Thinking AND is not allowed

5. You want to filter rows where column B contains the text 'apple' (case insensitive) using QUERY. Which WHERE clause is correct?

hard

A. WHERE lower(B) = 'apple'

B. WHERE B contains 'apple'

C. WHERE lower(B) contains 'apple'

D. WHERE B = 'apple'

Solution

1. Step 1: Understand case-insensitive text filtering

   To ignore case, convert text to lowercase using lower(), then check if it contains 'apple'.

2. Step 2: Evaluate each option

   A uses equality after lower() for exact match only; C uses contains without lower(), case-sensitive; D is case-sensitive exact equality; B uses lower() with contains correctly for case-insensitive partial match.

3. Final Answer:

   WHERE lower(B) contains 'apple' -> Option C

4. Quick Check:

   Use lower() with contains for case-insensitive filter [OK]

Hint: Use lower() with contains for case-insensitive text filter [OK]

Common Mistakes:

• Using = instead of contains for partial match
• Ignoring case sensitivity
• Using contains without lower()