How to Use Conditional Formatting in Google Sheets

What Is Conditional Formatting?

Conditional formatting is a built‑in feature that automatically changes a cell’s appearance—background colour, text colour, font style, etc.—based on the cell’s value or a custom formula you provide. In Google Sheets the rule is evaluated in real time, so any data change instantly updates the formatting.

What it does	Typical use case
Highlights cells that meet a numeric condition (e.g., > 100)	Spotting sales that exceed a target
Colours rows that contain a specific word (e.g., “Cancelled”)	Flagging orders that need attention
Applies a colour scale to a range of numbers	Visualising trends across a month
Uses a custom formula to compare values in different columns	Marking rows where “Start Date” is after “End Date”

How It Works

Select a range – tell Sheets which cells the rule applies to (e.g., A2:A100).
Define the condition – choose a preset (Greater than, Text contains, etc.) or write a custom formula that returns TRUE/FALSE.
Pick a formatting style – background colour, text colour, bold, data bar, colour scale, etc.

When the sheet recalculates, Sheets evaluates each cell in the range against the condition. If the result is TRUE, the formatting you set is applied; otherwise the cell remains unchanged.

Why It Matters

Instant visual cues – outliers, duplicates, or errors pop out at a glance.
Zero manual work – once a rule is set you never have to colour cells by hand again.
Dynamic dashboards – combine conditional formatting with charts or pivot tables for live, data‑driven reports.

Google Sheets‑Specific Notes

The custom‑formula option uses standard Sheets functions (=A2>100, =REGEXMATCH(B2,"error")).
Custom formulas must return TRUE or FALSE, and cell references are relative to the top-left cell of the range you selected.
Rules belong to the sheet they were created on. To reuse one elsewhere, copy the cells and use Paste special → Conditional formatting only.

Setting Up Basic Conditional Formatting Rules

The steps below work in the desktop web version of Google Sheets (any language setting).

Step	Action
1	Select the range you want to format (e.g., `A2:A10`).
2	Choose Format → Conditional formatting. The sidebar slides in from the right.
3	Verify or edit the Apply to range box.
4	Under Format cells if…, pick a rule type (e.g., Greater than, Text contains).
5	Enter the condition value (e.g., `>5000`, `"Pending"`).
6	Choose a formatting style (background colour, text colour, bold, etc.).
7	Click Done. Add more rules with Add another rule or edit/delete existing ones with the pencil/trash icons.

Quick Example: Highlight Low Sales

A (Sales)
7,200
4,850
3,100
9,450
2,750

Goal: colour any sales figure below 4,000 orange.

Select A2:A6.
Open Conditional formatting.
Set Format cells if… → Less than and type 4000.
Choose an orange background, then click Done.

The cells containing 3,100 and 2,750 now have an orange fill.

Using Formulas for Advanced Rules

Custom‑formula rules let you apply formatting based on any logic you can express in a cell formula. The formula is evaluated for each cell in the Apply to range you specify, and it must return TRUE for rows you want to highlight.

Goal	Example Formula (Google Sheets)	Explanation
Highlight rows where the due date is past and the task is not marked complete	`=AND($C2<TODAY(), $D2<>"Done")`	`$C` = due‑date column, `$D` = status column. `$` locks the column while the row number changes.
Shade duplicate values in column B	`=COUNTIF($B:$B, B2)>1`	Returns TRUE for every occurrence of a duplicate, not just the first one.
Flag sales > $10,000 or belonging to the “VIP” group	`=OR($E2>10000, $F2="VIP")`	Combines two independent conditions with `OR`.
Colour cells that start with “ERR”	`=REGEXMATCH($A2, "^ERR")`	`REGEXMATCH` works only in Sheets; it tests a regular‑expression pattern.
Apply a colour when a project status equals “In Progress”	`=$G2="In Progress"`	Simple equality test; `$` locks the column.

How to Set Up a Custom Formula

Select the range (e.g., A2:G100).
Open Format → Conditional formatting.
Change Format cells if… to Custom formula is.
Paste the formula, written as if it were entered in the top‑left cell of the range. Use $ to lock columns or rows as needed.
Choose the desired formatting style and click Done.

Common Pitfalls

Relative vs. absolute references – Forgetting $ can cause the rule to shift unexpectedly.
Functions not available in Excel – REGEXMATCH, REGEXEXTRACT, REGEXREPLACE, ARRAYFORMULA, and QUERY exist only in Sheets.
Errors in the formula – An error (#DIV/0!, #N/A, etc.) makes the rule evaluate to FALSE for that row; wrap the expression in IFERROR(...,FALSE) if needed.

Formatting Options

Google Sheets provides several built‑in styles that you can trigger with conditional formatting.

Formatting option	What it does	Typical use case
Background colour	Fills the cell with a solid colour.	Highlight rows that meet a sales target or flag overdue tasks.
Text colour	Changes the font colour only.	Emphasise negative numbers in red or positive numbers in green.
Bold / Italic / Underline / Strikethrough	Font‑weight or style changes.	Mark completed items (italic) or cancelled orders (strikethrough).
Colour scale (separate tab in the sidebar)	Applies a gradient (for example red to yellow to green) based on each cell's value relative to the rest of the range.	Visualise performance tiers across a numeric column.

Quick Tip

If the default palette doesn’t contain the colour you need, click Custom in the colour picker, enter a HEX or RGB value, and reuse that colour in future rules. Recently used custom colours appear in the palette for quick reuse.

Common Pitfalls and Troubleshooting

Symptom	Likely Cause	Fix
Rule doesn’t apply to new rows	Range is a fixed address (e.g., `A2:C10`).	Edit the rule to use an open‑ended range like `A2:C` or apply it to the whole column (`A:C`).
Colours flicker or disappear after reload	Custom formula returns an error for some rows.	Wrap the expression in `IFERROR(...,FALSE)`.
Multiple rules conflict	Two rules match the same cell and “stop if true” is unchecked.	Re‑order rules so the most important is on top, or enable “Stop if true”.
Text‑case mismatch	Formula uses case‑sensitive comparison (`=`) while data varies.	Normalise case with `UPPER()` or `LOWER()`, or use `REGEXMATCH`.
Formula pointing at another sheet shows "invalid formula"	Conditional formatting cannot reference another sheet directly.	Use `INDIRECT`: `=INDIRECT("Sheet1!$C2")="Done"`.
Performance slows on large data sets	Too many complex custom‑formula rules (especially with `ARRAYFORMULA` or `REGEXMATCH`).	Use built‑in options where possible, limit the applied range, and consolidate similar rules.

Quick Debug Checklist

Check the range – ensure it includes all target cells; use open‑ended references for expanding data.
Validate the formula – test it directly in a cell before adding it to a rule.
Watch for hidden characters – leading/trailing spaces or CHAR(160) can block matches; clean data with TRIM or CLEAN.
Confirm data type – dates stored as text won’t trigger date‑based rules; convert with DATEVALUE or re‑format the column.
Rule order matters – higher‑priority rules should be placed above lower‑priority ones, or use “Stop if true”.

FAQ

How do I highlight duplicate values in Google Sheets?

Use a custom formula that counts occurrences in the column:

=COUNTIF($B:$B, B2)>1

Apply this formula to the range you want to check (e.g., B2:B100) and choose a highlight colour. The rule returns TRUE for every duplicate entry, not just the first one.

Can I use conditional formatting with data from another sheet?

Not directly. A rule like =Sheet1!$C2="Done" is rejected as an invalid formula. Wrap the reference in INDIRECT instead:

=INDIRECT("Sheet1!$C2")="Done"

Apply it to the range you want to colour. The row number is relative, so each row checks the matching row on Sheet1. A second option is to copy the data into the current sheet with IMPORTRANGE or a simple reference, then base the rule on those cells.

What are the best practices for using color scales?

Limit the scale to a single numeric column to avoid confusing gradients.
Choose a colour palette that is colour‑blind friendly (e.g., blue‑orange).
Pair the scale with a clear legend or a nearby static key so viewers understand what the colours represent.

By mastering both the simple preset options and the more powerful custom‑formula approach, you can turn raw data into an instantly readable visual story—whether you’re building a personal tracker or a shared business dashboard. Happy formatting!

How to Use Conditional Formatting in Google Sheets