Bird
Raised Fist0
Google Sheetsspreadsheet~10 mins

Custom formula-based rules in Google Sheets - Interactive Code Practice

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Practice - 5 Tasks
Answer the questions below
1fill in blank
easy

Complete the formula to allow only positive numbers in cell A1.

Google Sheets
=IF([1], TRUE, FALSE)
Drag options to blanks, or click blank then click option'
AA1>0
BA1>=0
CA1=0
DA1<0
Attempts:
3 left
💡 Hint
Common Mistakes
Using less than or equal to zero instead of greater than zero.
Checking for equality to zero instead of greater than zero.
2fill in blank
medium

Complete the formula to allow only text entries in cell B2.

Google Sheets
=ISTEXT([1])
Drag options to blanks, or click blank then click option'
AC2
BA2
CB1
DB2
Attempts:
3 left
💡 Hint
Common Mistakes
Referencing the wrong cell.
Using a number instead of a cell reference.
3fill in blank
hard

Fix the error in the formula to allow only dates after January 1, 2023 in cell C3.

Google Sheets
=C3>[1]
Drag options to blanks, or click blank then click option'
ADATE(2023,1,1)
B"2023-01-01"
C1/1/2023
D2023-01-01
Attempts:
3 left
💡 Hint
Common Mistakes
Using a string instead of the DATE function.
Writing the date as a division expression.
4fill in blank
hard

Fill both blanks to allow only whole numbers between 1 and 100 in cell D4.

Google Sheets
=AND(ISNUMBER(D4), D4 [1] 1, D4 [2] 100)
Drag options to blanks, or click blank then click option'
A>=
B<=
C>
D<
Attempts:
3 left
💡 Hint
Common Mistakes
Using strict greater than or less than operators instead of inclusive ones.
Not checking if the value is a number.
5fill in blank
hard

Fill all three blanks to allow only text in cell E5 that starts with 'A' and has length 3.

Google Sheets
=AND(ISTEXT(E5), LEFT(E5, [1]) = [2], LEN(E5) [3] 3)
Drag options to blanks, or click blank then click option'
A1
B"A"
C=
D>
Attempts:
3 left
💡 Hint
Common Mistakes
Using greater than instead of equals for length.
Not quoting the letter 'A' in the comparison.

Practice

(1/5)
1. What does a custom formula in Google Sheets conditional formatting need to return to apply the format?
easy
A. A text string
B. FALSE
C. A number
D. TRUE

Solution

  1. Step 1: Understand conditional formatting rules

    Custom formulas in conditional formatting must evaluate to TRUE or FALSE to decide if formatting applies.

  2. Step 2: Identify the required return value

    Only when the formula returns TRUE does the formatting get applied to the cell.

  3. Final Answer:

    TRUE -> Option D

  4. Quick Check:

    Formula must return TRUE [OK]
Hint: Remember: TRUE applies formatting, FALSE does not [OK]
Common Mistakes:
  • Thinking the formula should return FALSE to apply formatting
  • Returning numbers instead of TRUE/FALSE
  • Returning text strings instead of logical values
2. Which of these is the correct syntax for a custom formula to highlight cells in column A that are greater than 100?
easy
A. =A1>100
B. =A>100
C. =A$1>100
D. =1A>100

Solution

  1. Step 1: Understand cell references in custom formulas

    Formulas must use a relative reference to the first cell in the range, here A1.

  2. Step 2: Check each option's syntax

    =A1>100 is correct; =A>100 is invalid because column alone is not a valid reference; =A$1>100 fixes row but not needed here; =1A>100 is invalid syntax.

  3. Final Answer:

    =A1>100 -> Option A

  4. Quick Check:

    Use relative cell reference like A1 [OK]
Hint: Use the first cell's reference in your formula [OK]
Common Mistakes:
  • Using only column letter without row number
  • Using invalid cell references like 1A
  • Fixing row or column unnecessarily
3. Given the range B2:B5 selected, what cells will be highlighted by the custom formula =B2>50?
medium
A. Only B2 if its value is greater than 50
B. All cells B2 to B5 where each cell's value is greater than 50
C. Only B5 if its value is greater than 50
D. No cells because formula only checks B2

Solution

  1. Step 1: Understand relative references in custom formulas

    The formula is written relative to the first cell B2, so it adjusts for each cell in the range.

  2. Step 2: Apply formula to each cell in B2:B5

    For B3, formula becomes B3>50; for B4, B4>50; for B5, B5>50. So all cells in the range are checked individually.

  3. Final Answer:

    All cells B2 to B5 where each cell's value is greater than 50 -> Option B

  4. Quick Check:

    Formula adjusts per cell in range [OK]
Hint: Formula adjusts relative to each cell in the range [OK]
Common Mistakes:
  • Thinking formula only checks the first cell
  • Assuming formula is fixed to B2 only
  • Not understanding relative references
4. You want to highlight rows where the value in column C is "Done". You select range A2:D10 and enter the formula =C2="Done". But no cells get highlighted. What is the problem?
medium
A. Formula should be =$C2="Done" to fix column C
B. Formula should be =C$2="Done" to fix row 2
C. Formula should use absolute reference $C$2
D. Formula should be =C2=Done without quotes

Solution

  1. Step 1: Understand how to fix column in custom formulas

    To apply the formula across rows but always check column C, fix the column with $ but keep row relative.

  2. Step 2: Correct the formula

    =$C2="Done" fixes column C, so for each row it checks the correct cell in column C.

  3. Final Answer:

    =$C2="Done" -> Option A

  4. Quick Check:

    Fix column with $ to check same column [OK]
Hint: Fix column with $ to check same column across rows [OK]
Common Mistakes:
  • Using absolute reference for both row and column
  • Not fixing column, so formula shifts incorrectly
  • Removing quotes around text in formula
5. You want to highlight cells in range A1:A10 that are either empty or contain a number less than 5. Which custom formula will work correctly?
hard
A. =AND(ISBLANK(A1), A1<5)
B. =OR(ISBLANK($A$1), $A$1<5)
C. =OR(ISBLANK(A1), A1<5)
D. =OR(ISBLANK(A$1), A$1<5)

Solution

  1. Step 1: Understand the condition logic

    We want to highlight if cell is empty OR if its value is less than 5.

  2. Step 2: Check formula correctness and references

    =OR(ISBLANK(A1), A1<5) uses OR with ISBLANK and A1<5, with relative reference A1, so it applies correctly to each cell in A1:A10.

  3. Final Answer:

    =OR(ISBLANK(A1), A1<5) -> Option C

  4. Quick Check:

    Use OR and relative reference for correct rule [OK]
Hint: Use OR for either condition and relative cell reference [OK]
Common Mistakes:
  • Using AND instead of OR
  • Fixing cell reference so formula doesn't adjust
  • Using wrong row or column references