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"))