Why advanced rules highlight patterns in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this conditional formatting formula?

You apply this custom formula in conditional formatting to cells A1:A5:

=AND(ISNUMBER(A1), MOD(A1, 2) = 0)

Which cells will be highlighted if the values are:
A1=3, A2=4, A3=7, A4=8, A5="text"?

A All cells will be highlighted

B Only A2 and A4 will be highlighted

C Only A1, A3, and A5 will be highlighted

D No cells will be highlighted

💡 Hint

Think about which numbers are even and which cells contain numbers.

The formula checks if the cell contains a number and if that number is even (divisible by 2). Only A2=4 and A4=8 meet both conditions.

❓ Function Choice

intermediate

Which function helps highlight duplicate values?

You want to create a conditional formatting rule that highlights cells with duplicate values in the range B1:B10. Which formula should you use?

A =B1 > 5

B =ISNUMBER(B1)

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

D =LEN(B1) = 0

💡 Hint

Think about counting how many times a value appears in the range.

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

❓ data_analysis

advanced

Analyzing pattern highlights with multiple conditions

You apply this conditional formatting formula to range C1:C6:

=AND(C1>10, ISODD(C1))

Given values:
C1=11, C2=12, C3=15, C4=8, C5=21, C6=10

Which cells will be highlighted?

A Only C1 and C5

B C2, C4, and C6

C Only C3 and C5

D C1, C3, and C5

💡 Hint

Check which numbers are greater than 10 and odd.

C1=11 (>10 and odd), C3=15 (>10 and odd), C5=21 (>10 and odd) meet both conditions.

🎯 Scenario

advanced

Highlighting cells based on text patterns

You want to highlight cells in column D that start with the letters "ex" (case insensitive). Which custom formula should you use in conditional formatting?

A =REGEXMATCH(LOWER(D1), "^ex")

B =LEFT(D1, 2) = "ex"

C =SEARCH("ex", D1) = 1

D =EXACT(LEFT(D1, 2), "ex")

💡 Hint

Consider case insensitivity and matching start of text.

REGEXMATCH with LOWER converts text to lowercase and checks if it starts with 'ex'. LEFT is case sensitive unless combined with LOWER.

🧠 Conceptual

expert

Why do advanced conditional formatting rules highlight unexpected cells?

You created a complex conditional formatting rule using this formula:

=AND(ISNUMBER(A1), A1>5, NOT(ISBLANK(A1)))

But some blank-looking cells are highlighted. What is the most likely reason?

A The cells contain invisible characters or spaces, so they are not truly blank

B The formula syntax is incorrect and highlights all cells

C Conditional formatting cannot detect blank cells

D The cells contain formulas returning empty strings, which count as blank

💡 Hint

Think about what makes a cell appear blank but still contain data.

Cells with spaces or invisible characters are not blank, so ISBLANK returns FALSE and the formula highlights them if other conditions match.