Managing multiple rules in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the result of applying these two conditional formatting rules?

You have two conditional formatting rules applied to cells A1:A3:

1. Rule 1: Format cells with value > 10 as red fill.
2. Rule 2: Format cells with value <= 10 as green fill.

Given the values in A1=8, A2=10, A3=12, what will be the fill color of cell A2?

A Red fill

B Both red and green fill applied

C No fill

D Green fill

💡 Hint

Think about which rule applies when the value is exactly 10.

Rule 2 applies to values less than or equal to 10, so cell A2 with value 10 gets green fill.

❓ Function Choice

intermediate

Which formula correctly applies multiple conditions in a single conditional formatting rule?

You want to highlight cells in B1:B5 that are greater than 5 and less than 15 using one conditional formatting rule. Which formula should you use?

A =AND(B1>5, B1<15)

B =OR(B1>5, B1<15)

C =B1>5<B15

D =IF(B1>5, B1<15)

💡 Hint

Use a function that checks if all conditions are true.

The AND function returns TRUE only if all conditions are met, so =AND(B1>5, B1<15) is correct.

❓ data_analysis

advanced

Analyzing rule precedence in multiple conditional formatting rules

You have three conditional formatting rules applied to range C1:C4 with these formulas and order:

1. =C1>100 (red fill)
2. =C1>50 (yellow fill)
3. =C1>0 (green fill)

If the values are C1=120, C2=75, C3=30, C4=0, what fill colors will each cell have assuming 'Stop If True' is enabled on all rules?

A C1: Red, C2: Yellow, C3: Green, C4: Green

B C1: Red, C2: Yellow, C3: Yellow, C4: Green

C C1: Red, C2: Yellow, C3: Green, C4: No fill

D C1: Red, C2: Red, C3: Green, C4: No fill

💡 Hint

Remember that 'Stop If True' stops checking further rules once a rule applies.

Each cell is colored by the first rule it meets. C1>100 is true, so red fill applies and stops. C2>50 is true, so yellow fill applies and stops. C3>50 is false, but C3>0 is true, so green fill applies. C4=0 does not meet any condition, so no fill.

🎯 Scenario

advanced

Scenario: Combining multiple conditional formatting rules with overlapping conditions

You want to highlight sales figures in D1:D5 with these rules:

• Highlight values >= 1000 with blue fill.
• Highlight values >= 1500 with bold text.
• Highlight values >= 2000 with red fill and bold text.

If the values are D1=900, D2=1200, D3=1600, D4=2100, D5=1800, what formatting will cell D4 have?

A Red fill and bold text

B Blue fill only

C Bold text only

D No formatting

💡 Hint

Check which rules apply to 2100 and how overlapping formats combine.

2100 meets all three conditions. The highest condition (>=2000) applies red fill and bold text. Since formatting can combine, D4 will have red fill and bold text.

📊 Formula Result

expert

What is the output of this formula used in a conditional formatting rule?

In cell E1, you apply this conditional formatting formula:

=AND(E1>10, OR(E1<20, E1=25))

What is the result (TRUE or FALSE) when E1 contains the value 25?

A FALSE

B TRUE

C Syntax Error

D #VALUE! Error

💡 Hint

Evaluate the AND and OR conditions step by step.

For E1=25: E1>10 is TRUE, OR(E1<20, E1=25) is TRUE because E1=25 is TRUE. AND(TRUE, TRUE) is TRUE.

Practice

1. What happens when you apply multiple conditional formatting rules to the same cell in Excel?

easy

A. All rules apply their formats simultaneously, layering colors and styles.

B. The rule listed first in the Rules Manager takes priority if rules conflict.

C. Only the last rule you created will apply, ignoring others.

D. Excel randomly chooses one rule to apply each time the sheet recalculates.

Solution

1. Step 1: Understand rule priority in Excel

   Excel applies conditional formatting rules in the order they appear in the Rules Manager.

2. Step 2: Effect of rule order on overlapping formats

   If multiple rules apply to the same cell and conflict, the first rule listed takes priority and its format is shown.

3. Final Answer:

   The rule listed first in the Rules Manager takes priority if rules conflict. -> Option B

4. Quick Check:

   Rule order controls format priority [OK]

Hint: Check rule order in Rules Manager to know which format shows [OK]

Common Mistakes:

• Assuming all formats stack visually
• Thinking last rule always wins
• Believing Excel picks rules randomly

2. Which of the following is the correct way to add a new conditional formatting rule in Excel?

easy

A. Go to Home tab > Conditional Formatting > Manage Rules > New Rule

B. Right-click cell > Insert > Conditional Formatting

C. File > Options > Conditional Formatting > Add Rule

D. Data tab > Sort & Filter > Conditional Formatting

Solution

1. Step 1: Locate Conditional Formatting options

   Conditional Formatting is found under the Home tab in Excel's ribbon.

2. Step 2: Add a new rule via Rules Manager

   Click Conditional Formatting, then Manage Rules, then New Rule to add a new formatting condition.

3. Final Answer:

   Go to Home tab > Conditional Formatting > Manage Rules > New Rule -> Option A

4. Quick Check:

   New rules added via Home > Conditional Formatting [OK]

Hint: Use Home tab > Conditional Formatting to manage rules [OK]

Common Mistakes:

• Looking for conditional formatting in wrong tabs
• Trying to add rules via right-click menus
• Confusing Conditional Formatting with sorting/filtering

3. Given these two conditional formatting rules on cell A1:
Rule 1: Format cell red if A1 > 10
Rule 2: Format cell green if A1 > 5
If A1 contains 12, what color will the cell be?

medium

A. Green

B. No color

C. Red

D. Yellow

Solution

1. Step 1: Check which rules apply to A1=12

   Since 12 > 10, Rule 1 applies; also 12 > 5, so Rule 2 applies too.

2. Step 2: Determine rule priority

   Rule 1 is listed first, so its red format takes priority over Rule 2's green.

3. Final Answer:

   Red -> Option C

4. Quick Check:

   First matching rule color shows [OK]

Hint: First true rule in order sets the cell color [OK]

Common Mistakes:

• Assuming green overrides red because 5 < 10
• Thinking colors combine to make yellow
• Ignoring rule order priority

4. You created two conditional formatting rules for cell B2:
1) Format blue if B2 < 50
2) Format yellow if B2 < 100
But when B2 is 40, the cell shows yellow instead of blue. What is the likely problem?

medium

A. The yellow rule is listed above the blue rule in the Rules Manager.

B. The formula for the blue rule is incorrect.

C. Excel cannot apply blue color formatting.

D. The cell B2 is formatted as text, so rules don't apply.

Solution

1. Step 1: Understand rule order effect

   If the yellow rule is above the blue rule, it applies first and stops further rules from applying.

2. Step 2: Check why blue doesn't show

   Since 40 < 100 is true, yellow applies first, so blue is ignored despite 40 < 50 also being true.

3. Final Answer:

   The yellow rule is listed above the blue rule in the Rules Manager. -> Option A

4. Quick Check:

   Rule order controls which format shows [OK]

Hint: Put more specific rules above general ones [OK]

Common Mistakes:

• Assuming formula error without checking rule order
• Thinking Excel can't show blue color
• Ignoring cell data type effects

5. You want to highlight sales figures in column C with these rules:
1) Red fill if sales < 1000
2) Orange fill if sales >= 1000
3) Green fill if sales > 5000

How should you order these rules in the Rules Manager to ensure correct formatting?

hard

A. Red first, then Orange, then Green

B. Orange first, then Red, then Green

C. Order does not matter for these rules

D. Green first, then Orange, then Red

Solution

1. Step 1: Analyze rule specificity

   Green applies to sales > 5000 (most specific highest values), Orange to mid-range, Red to lowest.

2. Step 2: Order rules from highest to lowest

   Place Green first so it applies before others, then Orange, then Red last for lowest values.

3. Final Answer:

   Green first, then Orange, then Red -> Option D

4. Quick Check:

   Order rules from highest to lowest value [OK]

Hint: Order rules from highest to lowest value for clarity [OK]

Common Mistakes:

• Putting red rule first causing it to override others
• Assuming order doesn't affect formatting
• Mixing up value ranges in rules