Conditional formatting basics in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What color will cell A1 be if its value is 75?

Given the conditional formatting rules below applied to cell A1, what color will A1 display if its value is 75?

• Rule 1: Format cells with value >= 90 as green
• Rule 2: Format cells with value >= 70 as yellow
• Rule 3: Format cells with value < 70 as red

Assume rules are applied in order and stop at the first true condition.

A Green

B Red

C No color

D Yellow

💡 Hint

Check which condition matches first when reading rules top to bottom.

75 is not >= 90, so Rule 1 is false. Rule 2 checks if value >= 70, which is true, so cell is colored yellow. Rule 3 is not checked because Rule 2 matched.

❓ Function Choice

intermediate

Which formula correctly highlights cells in column B if their value is less than the value in column A of the same row?

You want to apply conditional formatting to cells in column B. The formatting should highlight a cell if its value is less than the value in column A on the same row.

Which formula should you use in the conditional formatting rule?

A =B$1 < $A1

B =$B1 < $A1

C =B1 < A1

D =$B$1 < $A$1

💡 Hint

Remember the formula is evaluated for each cell in column B, so relative references matter.

Using =B1 < A1 with relative references allows the formula to adjust for each row. The other options fix rows or columns incorrectly, causing wrong comparisons.

❓ data_analysis

advanced

How many cells will be highlighted with this conditional formatting?

In the range C1:C5, the values are: 10, 20, 30, 40, 50.

A conditional formatting rule uses the formula =C1>25 applied to the range C1:C5.

How many cells will be highlighted?

A 3

B 2

C 4

D 5

💡 Hint

Check which values in the range are greater than 25.

Values greater than 25 are 30, 40, and 50, which are 3 cells. The formula adjusts for each cell in the range.

🎯 Scenario

advanced

You want to highlight duplicate values in column D. Which conditional formatting formula should you use?

To highlight duplicate values in column D (D1:D100), you create a new conditional formatting rule with a formula.

Which formula will correctly highlight duplicates?

A =COUNTIF($D$1:$D$100, D1) &gt; 1

B =COUNTIF(D1:D100, D1) &gt; 1

C =COUNTIF($D$1:D$100, $D1) &gt; 1

D =COUNTIF($D1:$D100, D1) &gt; 1

💡 Hint

Think about fixing the range and using relative reference for the cell being checked.

Option A fixes the range absolutely and uses a relative reference for the cell to check duplicates correctly for each cell in D1:D100.

🧠 Conceptual

expert

What happens if you apply two conflicting conditional formatting rules to the same cell?

You apply two conditional formatting rules to cell E1:

• Rule 1: If E1 > 50, fill color red
• Rule 2: If E1 > 30, fill color green

The rules are applied in the order listed, and both conditions are true for E1 = 60.

What will be the fill color of E1?

A Green, because the last rule overrides previous ones

B Red, because the first rule that matches applies and stops further rules

C No fill color, because conflicting rules cancel each other

D Both red and green fill colors combined

💡 Hint

Think about rule order and stopping behavior in conditional formatting.

Excel applies conditional formatting rules in order and stops at the first true condition if 'Stop If True' is enabled. So the first matching rule's format applies.