Conditional formatting changes how cells look — color, bold, strikethrough — based on rules you define. The most common use: highlight overdue tasks in red, mark completed rows in green, flag values above a threshold.

This tutorial covers the full setup, custom formula rules (the most powerful option), and common mistakes.

---

How to Open Conditional Formatting

  1. Select the cells you want to format
  2. Click Format > Conditional formatting
  3. A sidebar opens on the right

Every rule you create applies to the selected range and shows in this sidebar. You can have multiple rules on the same range — they stack in priority order.

---

The Three Ways to Set a Condition

1. Single color (rule-based)

Apply one format when a condition is true. Options include:

  • Is empty / Is not empty
  • Text contains / does not contain / starts with / ends with
  • Date is / is before / is after
  • Greater than / less than / equal to / between
  • Custom formula is — the most flexible option (covered below)

2. Color scale (gradient)

Applies a gradient across a range based on min/max values. Useful for heatmaps — showing which rows have the highest or lowest numbers without setting explicit thresholds.

3. Custom formula

The most powerful option. You write a formula that returns TRUE or FALSE — if TRUE, the format applies. This is how you highlight entire rows based on a single cell's value.

---

Basic Example: Highlight Cells Containing "Overdue"

  1. Select column C (the Status column)
  2. Open Format > Conditional formatting
  3. Under Format cells if, select Text contains
  4. Type Overdue
  5. Choose a red fill color
  6. Click Done

Every cell in column C that contains "Overdue" turns red.

---

Custom Formula Rules: Highlight Entire Rows

This is where conditional formatting becomes genuinely useful. Instead of coloring just the cell with "Overdue," you can color the entire row.

Select the full range you want to color — say, A2:E100.

Open conditional formatting. Under Format cells if, select Custom formula is.

Enter:

Code
=$C2="Overdue"

The $ in front of C locks the column — every cell in the row checks column C. The row number (2) is relative — it adjusts as the rule evaluates each row.

---

Locking the Column Reference ($C)

This trips people up. When a custom formula evaluates row 2, it checks $C2. When it evaluates row 3, it checks $C3. When it evaluates row 5 column D, it still checks $C5 — the column is locked but the row is relative.

If you write =C2="Overdue" without the $, Sheets will shift the column reference as it moves across columns. Row 2 column A checks C2, but row 2 column B checks D2. The entire row won't highlight correctly.

Rule: when using custom formulas for row highlighting, always lock the column with $.

---

Useful Custom Formula Examples

Highlight rows where a date is in the past:

Code
=$B2<TODAY()

Highlight rows where a date is within the next 7 days:

Code
=AND($B2>=TODAY(), $B2<=TODAY()+7)

Highlight rows where a checkbox is checked:

Code
=$D2=TRUE

Highlight duplicates in column A:

Code
=COUNTIF($A$2:$A$100,$A2)>1

Highlight every other row (zebra striping):

Code
=ISEVEN(ROW())

---

Managing Multiple Rules

When multiple conditional formatting rules apply to the same range, the first rule that matches wins. If you have:

  1. Row is red when Status = "Overdue"
  2. Row is yellow when Due Date is within 7 days

And a row is both overdue and within 7 days — it turns red (rule 1 wins).

To change the priority, drag rules up and down in the conditional formatting sidebar.

---

Common Problems

The entire sheet turns one color

Your formula is returning TRUE for every row, not just the ones you intended. Check the formula logic — a missing $, an off-by-one on the row number, or a formula that's always true.

Custom formula works on one cell but not the whole row

You forgot the $ before the column letter. Change =C2="Done" to =$C2="Done".

The rule applies to the wrong range

Check the "Apply to range" field at the top of the sidebar. It should be set to your full data range (e.g., A2:F100), not just a single column.

Rule stops highlighting after adding new rows

Your "Apply to range" ends at a specific row. Change it to a full column range like A2:F to cover all future rows.

---

What to Build on Top of This

Don't want to set this up yourself? Describe what you want to highlight and it'll be configured for your sheet. Get it installed