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.

Practice

1. What does a formula-based rule in Excel do when the formula returns TRUE?

easy

A. It changes the cell's appearance based on the rule.

B. It deletes the cell content.

C. It copies the cell to another sheet.

D. It locks the cell from editing.

Solution

1. Step 1: Understand formula-based rules

   Formula-based rules use formulas that return TRUE or FALSE to decide if formatting applies.

2. Step 2: Effect of TRUE result

   If the formula returns TRUE, Excel applies the formatting like color or font change to the cell.

3. Final Answer:

   It changes the cell's appearance based on the rule. -> Option A

4. Quick Check:

   TRUE formula triggers formatting [OK]

Hint: TRUE formulas trigger formatting changes in cells [OK]

Common Mistakes:

• Thinking the rule deletes or moves data
• Confusing formatting with locking cells
• Assuming FALSE triggers formatting

2. Which of these is the correct formula to highlight cells greater than 50 in conditional formatting?

easy

A. IF(A1>50)

B. A1>50

C. =A1>50

D. =IF(A1>50)

Solution

1. Step 1: Check formula syntax for conditional formatting

   Conditional formatting formulas must start with '=' and return TRUE or FALSE.

2. Step 2: Validate each option

   =A1>50 uses correct syntax and returns TRUE or FALSE directly. IF(A1>50) and A1>50 miss '='. =IF(A1>50) uses incomplete IF without value_if_true and value_if_false.

3. Final Answer:

   =A1>50 -> Option C

4. Quick Check:

   Formula must start with '=' and return TRUE/FALSE [OK]

Hint: Always start formulas with '=' in conditional formatting [OK]

Common Mistakes:

• Omitting '=' at the start
• Using incomplete IF statements
• Writing formulas that don't return TRUE/FALSE

3. Given the formula-based rule with formula =AND(A1>10, A1<20), which cells will be formatted?

medium

A. All cells regardless of value

B. Cells with values equal to 10 or 20

C. Cells with values less than 10 or greater than 20

D. Cells with values between 10 and 20, excluding 10 and 20

Solution

1. Step 1: Understand the AND formula conditions

   The formula checks if A1 is greater than 10 AND less than 20 simultaneously.

2. Step 2: Determine which values satisfy both conditions

   Only values strictly between 10 and 20 (not including 10 or 20) make the formula TRUE.

3. Final Answer:

   Cells with values between 10 and 20, excluding 10 and 20 -> Option D

4. Quick Check:

   AND(A1>10, A1<20) means 10 < A1 < 20 [OK]

Hint: AND means all conditions must be TRUE simultaneously [OK]

Common Mistakes:

• Including boundary values 10 or 20
• Confusing AND with OR logic
• Thinking it formats all cells

4. You wrote the formula =A1>100 for conditional formatting but no cells are highlighted even though some values are above 100. What is the likely problem?

medium

A. The formula is missing the '=' sign at the start.

B. The formula is correct but the range is not set properly.

C. The formula should use IF(A1>100) instead.

D. Conditional formatting does not support comparison operators.

Solution

1. Step 1: Check formula correctness

   The formula '=A1>100' is correct syntax for conditional formatting.

2. Step 2: Consider the formatting range

   If no cells highlight, likely the conditional formatting rule is applied to the wrong or empty range.

3. Final Answer:

   The formula is correct but the range is not set properly. -> Option B

4. Quick Check:

   Correct formula + wrong range = no formatting [OK]

Hint: Check the range where the rule is applied [OK]

Common Mistakes:

• Removing '=' sign
• Using IF unnecessarily
• Believing conditional formatting can't compare values

5. You want to highlight cells in column A that are either blank or contain a number less than 50. Which formula-based rule should you use?

hard

A. =OR(ISBLANK(A1), A1<50)

B. =AND(ISBLANK(A1), A1<50)

C. =IF(A1="", TRUE, A1<50)

D. =NOT(ISBLANK(A1))

Solution

1. Step 1: Understand the condition requirements

   We want to format cells that are blank OR have a number less than 50.

2. Step 2: Choose the correct logical formula

   OR(ISBLANK(A1), A1<50) returns TRUE if either condition is met, matching the requirement.

3. Final Answer:

   =OR(ISBLANK(A1), A1<50) -> Option A

4. Quick Check:

   Use OR for either condition TRUE [OK]

Hint: Use OR to combine multiple conditions [OK]

Common Mistakes:

• Using AND instead of OR
• Misusing IF inside conditional formatting
• Ignoring blank cells condition