0
0
Google Sheetsspreadsheet~15 mins

FILTER function in Google Sheets - Real Business Scenario

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a sales analyst at a retail company.
📋 Request: Your manager wants a list of all sales transactions where the sales amount is greater than $500.
📊 Data: You have a sales data table with columns: Transaction ID, Date, Salesperson, Region, and Sales Amount.
🎯 Deliverable: Create a filtered list showing only transactions with sales amounts greater than $500.
Progress0 / 3 steps
Sample Data
Transaction IDDateSalespersonRegionSales Amount
T0012024-05-01AliceNorth450
T0022024-05-02BobEast700
T0032024-05-03CharlieSouth300
T0042024-05-04DianaWest850
T0052024-05-05EvaNorth600
T0062024-05-06FrankEast200
T0072024-05-07GraceSouth950
T0082024-05-08HankWest400
1
Step 1: Select a blank cell where you want the filtered list to start, for example, cell G2.
Expected Result
The cell is ready to enter the formula.
2
Step 2: Enter the FILTER formula to show all rows where Sales Amount is greater than 500.
=FILTER(A2:E9, E2:E9 > 500)
Expected Result
The filtered list shows transactions T002, T004, T005, and T007 with all their details.
3
Step 3: Add headers above the filtered data to label the columns.
Manually type headers 'Transaction ID', 'Date', 'Salesperson', 'Region', 'Sales Amount' in cells G1 to K1.
Expected Result
Headers appear above the filtered data for clarity.
Final Result
Transaction ID | Date       | Salesperson | Region | Sales Amount
-------------- | ---------- | ----------- | ------ | ------------
T002           | 2024-05-02 | Bob         | East   | 700
T004           | 2024-05-04 | Diana       | West   | 850
T005           | 2024-05-05 | Eva         | North  | 600
T007           | 2024-05-07 | Grace       | South  | 950
✓Only transactions with sales amounts greater than $500 are shown.
✓This helps the manager focus on high-value sales quickly.
✓The FILTER function dynamically updates if the data changes.
Bonus Challenge

Modify the FILTER formula to show only transactions with sales amounts greater than $500 AND from the 'East' or 'West' regions.

Show Hint
Use the FILTER function with multiple conditions combined using the multiplication (*) sign for AND logic and plus (+) sign for OR logic, like: =FILTER(A2:E9, (E2:E9 > 500) * ((D2:D9 = "East") + (D2:D9 = "West")))