How to Highlight an Entire Row in Excel Based on a Cell Value

Step-by-step

The example is a task tracker whose Status column (column E) says Pending, In progress, Completed or Overdue. The goal: the whole row changes colour as soon as the status changes, with no formulas in the cells and no VBA.

1. Select the table without the headers

Drag across the whole table, starting from row 2. Excel only formats what you select.

2. Create a formula rule

Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. There’s no ready-made “highlight the whole row” option, so you write a short rule yourself.

3. Write the rule for Overdue

=$E2="Overdue"

In plain words: look at column E on this row, and if it says Overdue, format the row. The dollar sign locks the column, so every cell in the row checks column E. The row number has no dollar sign, so row 3 checks E3, row 4 checks E4 and so on. That’s why it’s $E2 and not $E$2.

4. Pick the format

Click Format > Fill and choose red or orange, and bold text if you like. Click OK twice. Every overdue row lights up, and changing a status updates the colour instantly.

5. Add a rule for each status

Repeat with =$E2="Completed" and a green fill, then =$E2="In progress" and yellow. Leave Pending white or give it a light grey. Soft shades are easier on the eye than bright ones, and you can change them any time in Manage Rules.

6. The quick alternative

Conditional Formatting > Highlight Cells Rules > Text that Contains needs no formula, but it usually colours only the cells that contain the word, not the whole row. Use it for a quick check, and the formula method when you want the full row.