Why rules-based formatting highlights patterns in Excel - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

Identify the highlighted cells with this rule

You apply this conditional formatting rule to cells A1:A5:

=MOD(A1,2)=0

Which cells will be highlighted if A1=3, A2=4, A3=7, A4=8, A5=10?

A A1, A3

B A2, A4, A5

C A1, A2, A3

D A3, A4

💡 Hint

Think about which numbers are even.

The formula checks if the number is even by using MOD with 2. Even numbers return 0 remainder, so cells with even numbers get highlighted.

❓ Function Choice

intermediate

Choose the correct formula for highlighting duplicates

You want to highlight duplicate values in the range B1:B10 using conditional formatting. Which formula should you use?

A =COUNTIF(B$1:B$10, B1)>1

B =SUM(B1:B10)>1

C =COUNT(B1)>1

D =IF(B1=B2, TRUE, FALSE)

💡 Hint

Count how many times the current cell value appears in the range.

COUNTIF counts how many times the value in B1 appears in the range B1:B10. If more than once, it is a duplicate and gets highlighted.

🎯 Scenario

advanced

Highlight cells with values above average

You want to highlight cells in C1:C20 that have values greater than the average of the range. Which conditional formatting formula will do this correctly?

A =AVERAGE(C1:C20)>C1

B =C1>AVERAGE(C1:C1)

C =C1>AVERAGE(C1:C20)

D =C1>AVERAGE(C$1:C$20)

💡 Hint

Use absolute references for the range in AVERAGE to keep it fixed.

Using C$1:C$20 fixes the range for all cells, so each cell compares its value to the average of the entire range.

❓ data_analysis

advanced

Find the pattern highlighted by this rule

You apply this conditional formatting rule to D1:D10:

=AND(D1>50, D1<100)

Which cells will be highlighted if the values are: 45, 55, 75, 100, 49, 51, 99, 101, 50, 80?

A Cells with values 45, 49, 50

B Cells with values 45, 55, 75

C Cells with values 55, 75, 51, 99, 80

D Cells with values 100, 101

💡 Hint

Look for values greater than 50 and less than 100.

The rule highlights cells where the value is more than 50 and less than 100, so only those values in that range get highlighted.

🧠 Conceptual

expert

Why does conditional formatting highlight patterns?

Which statement best explains why rules-based conditional formatting highlights patterns in data?

A It applies formulas or conditions to each cell and changes formatting when conditions are true, making patterns visible.

B It randomly colors cells to make the sheet look colorful and easier to read.

C It permanently changes the cell values to highlight important data.

D It hides cells that do not match the condition to focus only on matching data.

💡 Hint

Think about how conditional formatting works with rules and visual changes.

Conditional formatting uses rules to check each cell's value and changes the cell's look if the rule is true, helping users see patterns easily.