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.