Custom formula-based rules in Google Sheets - Step-by-Step Guide

Introduction

Custom formula-based rules let you change how cells look based on your own formulas. This helps highlight important data or spot trends by using your own conditions instead of preset ones.

When you want to highlight sales above a certain target that changes each month.

When you need to color rows where the date is older than today.

When you want to mark cells that contain errors or missing data.

When you want to highlight duplicate entries in a list.

When you want to show cells in red if a number is negative and green if positive.

Steps

Step 1: Select

- the range of cells you want to apply the rule to

The selected cells are highlighted and ready for formatting

💡 Select only the cells you want to format to avoid unwanted changes

Step 2: Click

- Format menu, then choose Conditional formatting

The Conditional format rules pane opens on the right side

Step 3: In the Conditional format rules pane, click

- the dropdown under Format cells if

A list of preset conditions appears

Step 4: Scroll down and select

- Custom formula is

A text box appears to enter your formula

Step 5: Type

- the custom formula box, for example: =A1>100

The formula is accepted and ready to apply

Step 6: Choose

- the formatting style (text color, fill color, etc.)

The preview shows how the formatting will look

Step 7: Click

- Done

The rule is applied and cells matching the formula change appearance

Before vs After

Before

A list of sales numbers in cells A1 to A10 with no special colors

After

Cells with sales above 100 in A1 to A10 have a green fill color

Settings Reference

Format cells if

📍 Conditional format rules pane

Choose the condition type for the formatting rule

Default: Cell is not empty

Custom formula

📍 Conditional format rules pane under Format cells if > Custom formula is

Enter your own formula to decide which cells get formatted

Default: None

Formatting style

📍 Conditional format rules pane

Set how the cells will look when the rule matches

Default: No formatting

Common Mistakes

Using relative references incorrectly in the formula

The formula may not apply correctly to all cells if references shift unexpectedly

Use absolute references with $ signs to fix columns or rows as needed, e.g., =$A1>100

Not starting the formula with =

Google Sheets will not recognize the input as a formula and the rule won't work

Always start your custom formula with an equal sign, like =A1>100

Applying the rule to the wrong range

Cells outside the intended range may get formatted or intended cells may be missed

Select the exact range before creating the rule or adjust the range in the Conditional format rules pane

Summary

Custom formula-based rules let you format cells based on your own conditions.

You write a formula starting with = that returns TRUE for cells to format.

Make sure to use correct cell references and select the right range before applying.

Practice

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