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.