Cross-column conditional rules in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to highlight sales records where the sales amount is above $500 and the customer rating is below 3. This will help identify high-value sales with low customer satisfaction.

📊 Data: You have a sales data table with columns: Order ID, Sales Amount, Customer Rating (1 to 5 scale), and Product Category.

🎯 Deliverable: Create a conditional formatting rule in Google Sheets that highlights rows meeting the criteria. Also, add a helper column that marks these rows with 'Check' for easy filtering.

Sample Data

Order ID	Sales Amount	Customer Rating	Product Category
1001	450	4	Electronics
1002	700	2	Home
1003	300	5	Garden
1004	800	1	Electronics
1005	200	3	Home
1006	650	4	Garden
1007	550	2	Electronics
1008	400	3	Home

1

Step 1: Add a new column named 'Check' in column E next to your data.

In cell E2, enter the formula: =IF(AND(B2>500, C2<3), "Check", "")

Expected Result

E2 shows 'Check' if Sales Amount > 500 and Customer Rating < 3, otherwise blank. For example, E2 is blank, E3 is 'Check' if conditions met.

2

Step 2: Copy the formula in E2 down to all rows of your data.

Drag the fill handle from E2 down to E9 (assuming 8 data rows).

Expected Result

Column E marks 'Check' only for rows where Sales Amount > 500 and Customer Rating < 3.

3

Step 3: Create a conditional formatting rule to highlight entire rows where the conditions are met.

Select range A2:D9. Go to Format > Conditional formatting. Under 'Custom formula is', enter: =AND($B2>500, $C2<3). Choose a fill color (e.g., light red). Click 'Done'.

Expected Result

Rows with Sales Amount > 500 and Customer Rating < 3 are highlighted in light red.

Final Result

Order ID | Sales Amount | Customer Rating | Product Category | Check
---------------------------------------------------------------
1001     | 450          | 4               | Electronics      | 
1002     | 700          | 2               | Home             | Check
1003     | 300          | 5               | Garden           | 
1004     | 800          | 1               | Electronics      | Check
1005     | 200          | 3               | Home             | 
1006     | 650          | 4               | Garden           | 
1007     | 550          | 2               | Electronics      | Check
1008     | 400          | 3               | Home             | 

✓Rows 1002, 1004, and 1007 have sales above $500 but customer ratings below 3.

✓These rows are marked with 'Check' and highlighted for easy review.

✓This helps focus on high-value sales that may need customer service attention.

Bonus Challenge

Create a summary table that counts how many 'Check' rows exist per Product Category.

Show Hint

Use the COUNTIFS function with criteria on the 'Check' column and Product Category column.