Formula-based 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 that meet certain conditions using formula-based rules in Excel. This will help quickly identify important trends and issues.

📊 Data: You have a table with sales data including columns for Salesperson, Region, Sales Amount, and Sales Target.

🎯 Deliverable: Create formula-based conditional formatting rules to highlight: sales below target in red, sales above target in green, and sales exactly on target in yellow.

Sample Data

Salesperson	Region	Sales Amount	Sales Target
Alice	North	1200	1500
Bob	South	1600	1500
Charlie	East	1500	1500
Diana	West	1400	1500
Eva	North	1700	1500
Frank	South	1500	1500
Grace	East	1300	1500
Hank	West	1550	1500

1

Step 1: Select the range of Sales Amount cells from C2 to C9.

No formula needed for selection.

Expected Result

Cells C2:C9 are selected.

2

Step 2: Create a conditional formatting rule to highlight sales below target in red.

Use formula: =C2<D2 with red fill color.

Expected Result

Cells where Sales Amount is less than Sales Target are highlighted red (C2, C5, C7).

3

Step 3: Create a conditional formatting rule to highlight sales above target in green.

Use formula: =C2>D2 with green fill color.

Expected Result

Cells where Sales Amount is greater than Sales Target are highlighted green (C3, C6, C9).

4

Step 4: Create a conditional formatting rule to highlight sales exactly on target in yellow.

Use formula: =C2=D2 with yellow fill color.

Expected Result

Cells where Sales Amount equals Sales Target are highlighted yellow (C4, C8).

5

Step 5: Verify that all conditional formatting rules apply correctly to the entire range C2:C9.

No formula, just check formatting.

Expected Result

Sales Amount cells are colored red, green, or yellow according to the rules.

Final Result

Sales Amount Highlighting

+------------+--------+--------------+--------------+
| Salesperson| Region | Sales Amount | Sales Target |
+------------+--------+--------------+--------------+
| Alice      | North  | 1200 (red)   | 1500         |
| Bob        | South  | 1600 (green) | 1500         |
| Charlie    | East   | 1500 (yellow)| 1500         |
| Diana      | West   | 1400 (red)   | 1500         |
| Eva        | North  | 1700 (green) | 1500         |
| Frank      | South  | 1500 (yellow)| 1500         |
| Grace      | East   | 1300 (red)   | 1500         |
| Hank       | West   | 1550 (green) | 1500         |
+------------+--------+--------------+--------------+

✓Sales below target are clearly marked in red for quick attention.

✓Sales above target are highlighted in green showing good performance.

✓Sales exactly meeting target are marked in yellow indicating target met.

Bonus Challenge

Add a new conditional formatting rule to highlight sales in the North region that are below target with a bold red font.

Show Hint

Use a formula combining region and sales comparison: =AND(B2="North", C2<D2) and set font color to red and font style to bold.