Managing multiple rules in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to highlight sales data with multiple conditions to quickly identify important trends and issues.

📊 Data: You have monthly sales data for different products, including sales amount, units sold, and region.

🎯 Deliverable: Create a spreadsheet with conditional formatting rules to highlight: 1) sales above $10,000 in green, 2) sales below $3,000 in red, and 3) units sold above 500 in blue.

Sample Data

Product	Region	Sales Amount	Units Sold
Widget A	North	12000	450
Widget B	South	2500	600
Widget C	East	8000	300
Widget D	West	15000	700
Widget E	North	2000	550
Widget F	South	11000	400
Widget G	East	3500	650
Widget H	West	5000	200

1

Step 1: Select the range of sales amount cells (C2:C9).

Expected Result

Cells C2 to C9 are selected.

2

Step 2: Create a conditional formatting rule to highlight sales above $10,000 in green.

Use formula: =C2>10000 and set fill color to green.

Expected Result

Cells with sales above 10000 (C2, C5, C6) are highlighted green.

3

Step 3: Create a conditional formatting rule to highlight sales below $3,000 in red.

Use formula: =C2<3000 and set fill color to red.

Expected Result

Cells with sales below 3000 (C3, C5) are highlighted red.

4

Step 4: Select the range of units sold cells (D2:D9).

Expected Result

Cells D2 to D9 are selected.

5

Step 5: Create a conditional formatting rule to highlight units sold above 500 in blue.

Use formula: =D2>500 and set fill color to blue.

Expected Result

Cells with units sold above 500 (D3, D5, D6, D7) are highlighted blue.

6

Step 6: Check that all rules apply correctly and do not conflict.

Expected Result

Sales above 10000 are green, sales below 3000 are red, units sold above 500 are blue. Cells with multiple conditions show the highest priority color.

Final Result

Green

↓

Red

↓

Green

↓

Red

↓

Green

↓

Blue

✓High sales (above $10,000) are clearly visible in green.

✓Low sales (below $3,000) are marked in red to catch attention.

✓High units sold (above 500) are highlighted in blue to identify popular products.

✓This helps quickly spot strong and weak sales performance.

Bonus Challenge

Add a new conditional formatting rule to highlight rows where sales are above $10,000 and units sold are below 500 with yellow fill.

Show Hint

Use a formula combining conditions with AND, like =AND($C2>10000,$D2<500) applied to the entire row range.