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.

Practice

1. What does conditional formatting do in Google Sheets?

easy

A. Deletes cells that contain errors

B. Automatically sorts data alphabetically

C. Changes cell colors based on rules to highlight data

D. Creates charts from selected data

Solution

1. Step 1: Understand the purpose of conditional formatting

   Conditional formatting changes how cells look based on conditions you set.

2. Step 2: Identify the main effect of conditional formatting

   It highlights important data by changing cell colors or styles.

3. Final Answer:

   Changes cell colors based on rules to highlight data -> Option C

4. Quick Check:

   Conditional formatting = Highlight data [OK]

Hint: Think: 'Highlight cells that meet conditions' [OK]

Common Mistakes:

• Confusing conditional formatting with sorting
• Thinking it deletes or moves data
• Assuming it creates charts automatically

2. Which of these is the correct way to start creating a conditional formatting rule in Google Sheets?

easy

A. Right-click cells and choose Delete

B. Select cells, then go to Format > Conditional formatting

C. Click Insert > Chart

D. Select cells and press Ctrl+C

Solution

1. Step 1: Recall how to access conditional formatting

   In Google Sheets, conditional formatting is found under the Format menu.

2. Step 2: Identify the correct menu path

   Select cells, then choose Format > Conditional formatting to start a rule.

3. Final Answer:

   Select cells, then go to Format > Conditional formatting -> Option B

4. Quick Check:

   Format menu > Conditional formatting [OK]

Hint: Look under the Format menu for conditional formatting [OK]

Common Mistakes:

• Trying to create rules from Insert menu
• Using copy or delete commands instead
• Right-click menu does not have conditional formatting

3. You apply this conditional formatting rule to cells A1:A5: Format cells if 'Cell is greater than' 50 with red fill. If the values are 30, 55, 50, 70, 45, which cells will be red?

medium

A. A2 and A4 and A3

B. A1, A3, and A5

C. A2, A3, and A4 only

D. A2 and A4

Solution

1. Step 1: Identify which cells have values greater than 50

   Values: A1=30, A2=55, A3=50, A4=70, A5=45. Greater than 50 means strictly more than 50, so A2=55 and A4=70 qualify.

2. Step 2: Determine which cells get red fill

   Only cells with values greater than 50 get red fill, so A2 and A4 are red.

3. Final Answer:

   A2 and A4 -> Option D

4. Quick Check:

   Greater than 50 = A2, A4 [OK]

Hint: Check values strictly greater than 50, not equal [OK]

Common Mistakes:

• Including cells equal to 50
• Confusing 'greater than' with 'greater or equal'
• Selecting wrong cells by mistake

4. You want to highlight cells in B1:B5 that contain the word "Done" using conditional formatting with a custom formula. Which formula is correct?

medium

A. =B1="Done"

B. =IF(B1=Done, TRUE, FALSE)

C. =B1=Done

D. =B1 contains "Done"

Solution

1. Step 1: Understand how to write a custom formula for conditional formatting

   The formula must return TRUE for cells to be formatted. Comparing text requires quotes around the word.

2. Step 2: Check each option's correctness

   =B1="Done" uses =B1="Done" which returns TRUE if B1 equals "Done". =IF(B1=Done, TRUE, FALSE) lacks quotes around Done, causing error. =B1=Done lacks quotes around Done, causing error. =B1 contains "Done" is invalid syntax.

3. Final Answer:

   =B1="Done" -> Option A

4. Quick Check:

   Use =B1="Done" for exact text match [OK]

Hint: Use =Cell="Text" with quotes for text matching [OK]

Common Mistakes:

• Omitting quotes around text
• Using IF unnecessarily in conditional formatting
• Writing invalid syntax like 'contains'

5. You want to highlight all rows in A2:D10 where the value in column C is less than 100. Which conditional formatting custom formula should you use when applying to range A2:D10?

hard

A. =$C2<100

B. =C2<100

C. =$C$2<100

D. =C$2<100

Solution

1. Step 1: Understand how to apply conditional formatting to entire rows

   To highlight entire rows based on column C, fix the column with $ but allow row number to change.

2. Step 2: Analyze each formula for correct referencing

   =$C2<100 uses =$C2<100 which fixes column C but lets row number adjust per row. =C2<100 lacks $ fixing column, so column shifts incorrectly. =$C$2<100 fixes both column and row, so only row 2 is checked. =C$2<100 fixes row but not column, so column shifts incorrectly.

3. Final Answer:

   =$C2<100 -> Option A

4. Quick Check:

   Fix column with $ for row-based rules [OK]

Hint: Fix column with $ to apply rule per row [OK]

Common Mistakes:

• Fixing row number instead of column
• Not fixing column causing wrong columns checked
• Using absolute references that don't adjust per row