Conditional formatting in Google Sheets usually colors the cell you applied it to. To color an entire row — say, turning a whole row red when a status says "Overdue," or green when a project is "Done" — you need a custom formula instead of the built-in rules.

This tutorial covers how to do that, including how to highlight based on exact text, partial text, dates, and numbers.

---

What You'll Have by the End

A Google Sheet where entire rows change color automatically based on the value in one column — no manual formatting, no checking every row.

---

The Core Concept: Custom Formula + Locked Column

Standard conditional formatting rules ask "what does this cell contain?" A custom formula asks "is this condition true?" — and you can write that formula to reference any column, not just the one being formatted.

The key is the $ sign. When you apply a rule to a range like A2:E100 and write a formula like =$D2="Done", the $ before D locks the column — so every cell in the row checks column D — but the 2 moves down with each row. That's how the whole row responds to one column's value.

---

Step 1: Select the Entire Range You Want to Color

Click the first cell of your data (usually A2 — the cell below your header row) and drag to the last column and a generous number of rows down. If your sheet has 5 columns and you want to cover 200 rows of data, select A2:E200.

Don't select row 1 if it's a header — the header will get highlighted too if a value there matches your rule.

---

Step 2: Open Conditional Formatting

With your range selected:

  1. Click Format in the top menu
  2. Click Conditional formatting

A panel opens on the right side of the screen.

---

Step 3: Set Up a Custom Formula Rule

In the conditional formatting panel:

  1. Under Format rules, click the dropdown (it defaults to "Cell is not empty")
  2. Scroll to the bottom and select Custom formula is
  3. A text field appears — this is where you type your formula

---

Step 4: Write the Formula for Your Condition

Here are the most common scenarios:

Highlight rows where a column contains exact text

Code
=$D2="Done"

Change D to the column letter you're checking. Change "Done" to the exact text you're looking for. Case-sensitive — "done" and "Done" are treated as different values.

Highlight rows where a column contains partial text

Code
=SEARCH("urgent", $D2)

SEARCH looks for the word "urgent" anywhere inside the cell in column D. Not case-sensitive. If the text isn't found, the formula returns an error (which counts as false), so no color. If found, returns a number (which counts as true), so the row highlights.

Highlight rows where a date is in the past (overdue)

Code
=$C2<TODAY()

Highlights every row where the date in column C is before today. Combine with a status check to skip completed items:

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

This highlights overdue rows where the status isn't "Done."

Highlight rows where a number is below a threshold

Code
=$B2<=5

Highlights rows where the value in column B is 5 or less. This is the same pattern used in the Inventory Tracker with Low-Stock Alerts to flag items that need reordering.

---

Step 5: Choose the Highlight Color

Below the formula field, click the fill color button and choose a color. Then click Done.

The rows matching your condition should highlight immediately.

---

Step 6: Add More Rules for Multiple States

To highlight different rows in different colors — "Done" in green, "Overdue" in red, "In Progress" in yellow — add a separate rule for each:

  1. In the conditional formatting panel, click + Add another rule
  2. Make sure the range is the same (A2:E200)
  3. Write the new formula and pick a different color
  4. Click Done

Google Sheets evaluates rules top to bottom and stops at the first match. If you want "Overdue" to take priority over "In Progress" when both could apply, put the Overdue rule above the In Progress rule in the list.

To reorder rules: click the six dots on the left side of a rule and drag it up or down.

---

Common Problems

The entire sheet turns one color. The formula is applying to the header row too. Check that your selected range starts at row 2, not row 1.

Only the first cell in each row highlights, not the whole row. The range you applied the rule to is a single column instead of the full row range. Select all columns (e.g., A2:E200) before setting up the rule.

The formula isn't working — nothing highlights. Check three things: (1) the column letter is right and has a $ in front of it, (2) the text in the formula exactly matches what's in the cell (including capitalization for exact-match formulas), (3) your range starts at the correct row.

The wrong rows are highlighting. The $ is in the wrong place. The formula should lock the column ($D) but not the row number — $D2, not $D$2. If you write $D$2, every row checks only row 2 instead of moving down.

---

What to Do Next

Conditional formatting handles the visual layer. Once you can see which rows need action, the next step is getting alerted about them without checking the sheet manually.

Don't want to set this up yourself? Describe what you want highlighted and under what conditions, and it'll be configured for you. Get it installed