How to Highlight an Entire Row With Conditional Formatting

Built-in conditional formatting colors the cell that meets a condition. That is fine for a column of numbers, but in a task tracker you usually want the whole row to turn red when a task is overdue, so it is visible from anywhere on the screen. Both Excel and Google Sheets can do this with a custom formula rule, and the trick that makes it work is one dollar sign.

1. The idea behind formula rules

Microsoft's documentation describes formula rules as a way to go beyond the built-in options. You write a formula that returns TRUE or FALSE, and the cell is formatted when the result is TRUE. Google's help page puts it the same way: custom formulas let you format cells based on the contents of other cells.

You write the formula for the first row of the range you selected, and the spreadsheet adjusts it for every other cell, just as it would if you copied a normal formula.

2. Google's example: color the row when column B says Yes

Google's documented steps for formatting an entire row based on one of its cells:

1. Select the range, for example columns A:E.
2. Click Format > Conditional formatting.
3. Under "Format cells if," choose Custom formula is.
4. Write the rule for the first row. Google's example, to make the whole row green when column B is "Yes":

=$B1="Yes"

5. Choose the formatting style and click Done.

3. Why the dollar sign goes before the column

The rule is evaluated for every cell in A:E. For cell C1, the spreadsheet shifts the formula as if it had been copied one or two columns to the right. Without a dollar sign, B1 would become D1 by the time it reached cell D1, and each cell would be testing a different column.

The $ in $B1 locks the column, so every cell in the row tests column B. The row number stays relative, so row 2 tests B2, row 3 tests B3, and so on.

You can see the shift directly by entering the formula in ordinary cells. Fill =$D2="Done" from J2 across to M2, and M2 still reads =$D2="Done". Fill =D3="Done" from J3 across to M3, and M3 becomes =G3="Done", testing the wrong column entirely. Google's page makes the same point: you will often need dollar signs so the formatting uses the references you intend.

4. A worked example: overdue tasks

Here is a tracker in A1:D5, with column C holding due dates:

A task is overdue when its due date is before today and it is not done. Select A2:D5 and use this rule:

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

<> means "is not equal to." TODAY returns the current date, and Microsoft's Mac guide uses it the same way in its own example, =A2>TODAY(), to format future birthdays.

With today as October 11, 2026, the rule returns:

• Draft budget: FALSE (past due date, but Done)
• Vendor quote: TRUE (past due, In progress)
• Team survey: FALSE (not due yet)
• Q3 report: TRUE (past due, Blocked)

So rows 3 and 5 turn red. Because TODAY updates every day, the highlighting updates too, with no editing. Tomorrow nothing changes, but on October 21 the survey row will turn red if it is still not done.

Before trusting a rule, you can test the formula in a spare column. Enter =AND($C2<TODAY(),$D2<>"Done") in E2, fill it down, and check that the TRUE rows are the ones you expect. Then delete the helper column.

5. Doing it in Excel

The steps differ slightly by version, but follow the same pattern. In Microsoft's guide for Excel for Mac:

1. Select the range (for this example, A2:D5).
2. On the Home tab, click Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type the formula, for example =AND($C2<TODAY(),$D2<>"Done").
5. Choose a format and click OK.

The same dollar sign rule applies: lock the column, leave the row free, and write the formula for the first row of your selection. If your selection starts at row 2, the formula must refer to row 2.

6. Stacking more than one rule

You might want overdue rows in red and blocked rows in yellow. Add a second rule, =$D2="Blocked", which returns TRUE only for the Q3 report row.

Now the Q3 report row matches both rules. Google's documentation explains how that is resolved in Sheets: rules are evaluated in the order listed, and the first rule found to be true defines the format. Drag the rule you care about most to the top. Google also notes that copying and pasting cells with formatting rules carries the rules to the pasted location, which is worth knowing before you paste a formatted block into another sheet.

7. Key takeaways

• A custom formula rule formats a cell when the formula returns TRUE.
• Write the formula for the first row of the selected range.
• Put a dollar sign before the column (as in $D2) so every cell in the row tests the same column.
• =AND($C2<TODAY(),$D2<>"Done") highlights overdue, unfinished tasks and updates daily.
• In Google Sheets, the first matching rule in the list wins, so order your rules deliberately.