Google Sheets

Google Sheets Conditional Formatting

Use Google Sheets conditional formatting to highlight statuses, overdue dates, duplicates, thresholds, and rows that need review.

Do this with AI

Try Griddy free

Start in your browser. Also works with Excel and Google Sheets.

By Justin Freels/Updated August 20, 2026/5 min read

Google Sheets conditional formatting changes cell styling when a rule is true. Use it to highlight overdue tasks, blocked work, duplicate values, budget overruns, missing fields, or anything else that should stand out during review.

The point is not decoration. Good conditional formatting turns a dense spreadsheet into a decision surface.

Create a basic conditional formatting rule

Step 1. Select the range you want to format, such as A2:H200.

Step 2. Go to Format -> Conditional formatting.

Step 3. Choose the condition, such as Text contains, Date is before, Greater than, or Custom formula is.

Step 4. Pick the formatting style.

Step 5. Click Done.

Start with one or two high-signal rules. Too many colors make the sheet harder to read.

Highlight blocked tasks

If column D contains task status, select the rows in the tracker and use a custom formula:

=$D2="Blocked"

Apply a red or amber fill to the row. The dollar sign locks the status column while allowing the row number to move.

This is useful in a project tracker, content calendar, or social media calendar where the review meeting should focus on blocked items first.

Highlight overdue dates

If column E contains due dates and column D contains status, use:

=AND($E2<TODAY(), $D2<>"Done")

That highlights rows where the due date is before today and the task is not complete.

TIP

Use row-based formatting for trackers. It is easier to scan than highlighting only the individual due-date cell.

Highlight budget overruns

For a finance sheet with planned amount in column C and actual amount in column D, use:

=$D2>$C2

That makes overspending visible as soon as actuals exceed plan. It works well in a budget tracker, small-business budget, or expense tracker.

Highlight missing required fields

To flag rows where a task name exists but owner is blank, use:

=AND($A2<>"", $B2="")

This catches incomplete rows without highlighting every empty row in the sheet.

Common conditional formatting mistakes

Mistake	Why it breaks review	Better approach
Formatting entire blank rows	The sheet looks broken before data exists	Require a key field like task name first
Using too many colors	Users stop understanding the signal	Limit rules to risk, status, and missing data
Forgetting dollar signs	Rules drift across columns	Lock the key column, such as `$D2`
Conflicting rules	One rule hides another	Put the highest-priority rule first

Keep rules ordered and bounded

Apply conditional formatting to the actual data range rather than an entire sheet when possible. Large, overlapping ranges make a workbook slower and make it harder to tell which rule caused a color. Google Sheets evaluates rules in order; if multiple rules can match the same row, put the most important signal first and test the overlap deliberately.

For row-based rules, write the formula relative to the first row of the selected range and lock only the column that supplies the signal. For example, =$D2="Blocked" works when the selected range starts on row 2. If the range starts on row 5, the formula should use row 5. A correct formula with the wrong starting row can silently color the wrong records.

Use a small legend for colors and include a non-color cue such as a status label or icon. That keeps the sheet understandable when printed or viewed by someone with limited color perception.

Sources

The Griddy way

Conditional formatting takes time because the useful rules depend on the actual columns, not a generic example.

"Highlight overdue tasks, blocked rows, overspent budget lines, and missing owners in this Google Sheets file"

Griddy can add the rules to the right ranges and keep the formatting focused on the signals that matter.

Do this with AI

Turn the instructions into a finished spreadsheet.

Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.

Try Griddy freeStart in your browser. No download required.

Also works withExcel Google Sheets

Use this on real templates

Make the important rows stand out

Conditional formatting helps operating templates surface blocked work, overdue dates, budget overruns, and missing fields before review starts.

Project Management

All posts