Conditional formatting basics in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

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

You apply this conditional formatting rule to cell A1:

=A1 > 50

The formatting sets the cell background color to green if the condition is true.

If A1 contains the number 75, what will be the background color of A1?

A Blue

B Red

C Green

D No color (default)

💡 Hint

Think about whether 75 is greater than 50.

The formula =A1 > 50 checks if the value in A1 is greater than 50. Since 75 is greater than 50, the condition is true, so the cell background becomes green.

❓ Function Choice

intermediate

Which formula highlights cells with text containing 'apple'?

You want to highlight cells in column B that contain the word 'apple' anywhere in the text. Which formula should you use in conditional formatting?

A =SEARCH("apple", B1)

B =ISNUMBER(SEARCH("apple", B1))

C =B1 = "apple"

D =FIND("apple", B1) > 0

💡 Hint

SEARCH returns a number if found, but the formula needs to be TRUE or FALSE.

The formula =ISNUMBER(SEARCH("apple", B1)) returns TRUE if 'apple' is found anywhere in B1 text. This makes the conditional formatting apply correctly.

Option B returns a number but not TRUE/FALSE, so it won't work as expected.

Option B only matches exact text 'apple', not partial.

Option B uses FIND which is case-sensitive and returns an error if not found, so it can cause errors.

🎯 Scenario

advanced

Highlight rows where sales are above average

You have sales data in column C (cells C2:C20). You want to highlight the entire row (columns A to D) if the sales value in column C is greater than the average sales in that range.

Which conditional formatting formula should you use for the range A2:D20?

A =$C2 > AVERAGE($C$2:$C$20)

B =$C$2 > AVERAGE($C$2:$C$20)

C =C2 > AVERAGE(C2:C20)

D =$C2 > AVERAGE(C2:C20)

💡 Hint

Remember to fix the column but allow the row to change as the formatting applies to each row.

The formula =$C2 > AVERAGE($C$2:$C$20) fixes the column C with $ so it always checks column C for each row, but the row number changes as the formatting applies down the rows.

Option A fixes both column and row, so it always checks only C2, not the current row.

Option A does not fix column, so it shifts columns when applied across columns A to D, causing wrong checks.

Option A uses relative range in AVERAGE which can shift incorrectly.

📊 Formula Result

advanced

What happens if you use =ISBLANK(A1) in conditional formatting?

You apply the formula =ISBLANK(A1) as a conditional formatting rule to cell A1. What will be the result if A1 contains a formula that returns an empty string ""?

A The cell will not be highlighted because ISBLANK returns FALSE for empty string.

B The cell will be highlighted because ISBLANK returns TRUE for empty string.

C The cell will show an error because ISBLANK cannot be used with formulas.

D The cell will be highlighted only if the formula returns zero.

💡 Hint

Think about what ISBLANK considers blank.

ISBLANK returns TRUE only if the cell is truly empty (no content). A formula returning an empty string "" is not considered blank, so ISBLANK returns FALSE and the cell is not highlighted.

❓ data_analysis

expert

Count how many cells are highlighted by this conditional formatting

You apply this conditional formatting rule to range B2:B10:

=MOD(B2, 2) = 0

This highlights cells with even numbers.

If the values in B2:B10 are: 3, 4, 7, 8, 10, 13, 14, 15, 16, how many cells will be highlighted?

A 7

B 4

C 6

D 5

💡 Hint

Count how many numbers in the list are even.

The formula =MOD(B2, 2) = 0 highlights even numbers.

Values: 3(odd), 4(even), 7(odd), 8(even), 10(even), 13(odd), 14(even), 15(odd), 16(even).

Even numbers are 4, 8, 10, 14, 16 → 5 numbers.

So 5 cells should be highlighted.