Office Dễ Hiểu

How to highlight an entire row in Excel by status

Use a conditional formatting formula to highlight overdue tasks.

Select the full data range and create a formula rule that locks the status column while allowing the row to change.

Visual walkthrough

HomeConditional FormattingNew Rule

Before

A2:D20 is the target range. D4 contains Overdue.

After

The full row A4:D4 changes color.

Check the result

Change D4 to Completed. The highlight should disappear.

Step-by-step instructions

  1. Select A2:D20, with row 2 as the first data row.
  2. Choose Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter the formula below.
    =$D2="Overdue"
  3. Choose a fill color, then confirm. In Manage Rules, check that Applies to is =$A$2:$D$20.
  4. Set D4 to Overdue and check that all cells in A4:D4 are highlighted. Change it to Completed and confirm that the highlight disappears.

Frequently asked questions

Why is the wrong row highlighted?

Check the first row of Applies to and the row reference in the formula. For a range starting at row 2, the formula should also use row 2.

Reference: Microsoft Support