Advanced filtering criteria in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a list of sales records where the sales amount is greater than $500 and the region is either East or West.

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

🎯 Deliverable: Create a filtered list showing only the sales records that meet the criteria: Sales Amount > 500 and Region is East or West.

Sample Data

Order ID	Date	Region	Salesperson	Product	Quantity	Sales Amount
1001	2024-01-05	East	Alice	Widget	10	600
1002	2024-01-06	West	Bob	Gadget	5	450
1003	2024-01-07	North	Charlie	Widget	7	700
1004	2024-01-08	East	Diana	Gizmo	3	300
1005	2024-01-09	West	Eva	Widget	12	1200
1006	2024-01-10	South	Frank	Gadget	4	400
1007	2024-01-11	East	Grace	Gizmo	6	650
1008	2024-01-12	West	Hank	Widget	8	800

1

Step 1: Select the entire sales data table including headers.

Click and drag from cell A1 to G9 to select all data.

Expected Result

The whole table is highlighted for filtering.

2

Step 2: Turn on the filter feature to enable filtering on each column.

Go to the Data tab and click on 'Filter'.

Expected Result

Filter dropdown arrows appear in each header cell.

3

Step 3: Apply a number filter on the 'Sales Amount' column to show only values greater than 500.

Click the filter arrow on 'Sales Amount' column, choose 'Number Filters' > 'Greater Than...', enter 500, and click OK.

Expected Result

Only rows with Sales Amount greater than 500 remain visible.

4

Step 4: Apply a text filter on the 'Region' column to show only rows where Region is East or West.

Click the filter arrow on 'Region' column, choose 'Text Filters' > 'Custom Filter', select 'equals' 'East' OR 'equals' 'West', then click OK.

Expected Result

Only rows with Region East or West and Sales Amount > 500 are visible.

5

Step 5: Review the filtered list to confirm it matches the criteria.

No formula; visually check the filtered rows.

Expected Result

Visible rows are Order IDs 1001, 1005, 1007, and 1008.

Final Result

Order ID | Date       | Region | Salesperson | Product | Quantity | Sales Amount
---------|------------|--------|-------------|---------|----------|-------------
1001     | 2024-01-05 | East   | Alice       | Widget  | 10       | 600         
1005     | 2024-01-09 | West   | Eva         | Widget  | 12       | 1200        
1007     | 2024-01-11 | East   | Grace       | Gizmo   | 6        | 650         
1008     | 2024-01-12 | West   | Hank        | Widget  | 8        | 800         

✓Only sales with amount greater than $500 and region East or West are shown.

✓Order IDs 1001, 1005, 1007, and 1008 meet the criteria.

✓This filtered list helps focus on high-value sales in key regions.

Bonus Challenge

Create a formula-based helper column that marks rows meeting the criteria with 'Yes' and then filter by this column.

Show Hint

Use the formula =AND(G2>500, OR(C2="East", C2="West")) in a new column and filter by TRUE.