Conditional formatting basics in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply conditional formatting that highlights cells greater than 100.

Google Sheets

Format cells if: Cell value [1] 100

Drag options to blanks, or click blank then click option'

Ais not equal to

Bis less than

Cis equal to

Dis greater than

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'is less than' will highlight smaller numbers, not bigger ones.

Using 'is equal to' only highlights cells exactly 100.

The rule should highlight cells with values greater than 100, so use 'is greater than'.

2fill in blank

medium

Complete the formula to highlight cells in column A that contain the word "Done".

Google Sheets

Custom formula is: =ISNUMBER([1](A1, "Done"))

Drag options to blanks, or click blank then click option'

AISNUMBER

BSEARCH

CLEN

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using ISNUMBER alone won't find text, it checks if a value is a number.

LEN counts characters, not search for text.

The SEARCH function looks for text inside a cell. It returns a number if found, so it works for this condition.

3fill in blank

hard

Fix the error in the formula to highlight cells in B2:B10 that are empty.

Google Sheets

=ISBLANK([1])

Drag options to blanks, or click blank then click option'

AB2

BB2:B

CB$2:B$10

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range like B2:B10 inside ISBLANK causes errors.

Using absolute references ($) is not needed here.

Conditional formatting formulas apply to each cell individually. Use a single cell reference like B2, not a range.

4fill in blank

hard

Fill both blanks to create a formula that highlights cells in C2:C20 if the value is between 50 and 100.

Google Sheets

=AND(C2 [1] 50, C2 [2] 100)

Drag options to blanks, or click blank then click option'

A>=

B<=

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using > or < excludes the boundary values.

Mixing up the order of operators causes wrong results.

The formula checks if C2 is greater than or equal to 50 and less than or equal to 100.

5fill in blank

hard

Fill all three blanks to create a formula that highlights cells in D2:D15 if the text starts with "Urgent".

Google Sheets

=LEFT([1], [2]) = [3]

Drag options to blanks, or click blank then click option'

AD2

B6

C"Urgent"

D7

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference.

Using the wrong number of characters in LEFT.

Not putting the text in quotes.

The LEFT function extracts the first 6 characters from D2 and compares them to the text "Urgent".

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