Highlight an Entire Row Based on a Cell Value

To highlight a row based on a cell value, use a conditional formatting rule with a formula such as =$D2="Late". Every cell in the row checks the same column, so when the status is Late, the whole row turns red. This guide walks through the steps, explains why the $ goes where it does, and gives rules for numbers, dates, text and several conditions.

How to highlight a row based on a cell value#

The examples use this order list in A1:E9. The goal is to colour every row where the Status in column D is Late:

1. Select A2:E9: every column you want coloured, starting at row 2 so the header stays plain.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type =$D2="Late" in the box.
5. Click Format, choose a light red on the Fill tab, and click OK twice.

Rows 3, 5 and 7 (orders 1002, 1004 and 1006) turn red across all five columns. The menus are the same in Excel for Mac.

Why the $ goes before the column only#

The formula is written for the top-left cell, A2. Excel then shifts it for every other cell, just like copying a formula. The $ controls what shifts:

With $D2, the column is locked to D, so every cell in row 2 looks at D2, and every cell in row 3 looks at D3. That's what makes the whole row act as one. Without the $, cell B2 checks E2, cell C2 checks F2 and so on, and you get a patchy result.

The row number has no $, so it moves down: row 3 checks D3, row 4 checks D4. Writing =$D$2 would lock the row too, and every row would copy row 2's answer. For a refresher on these reference types, see cell references explained.

Row highlighting rules for numbers, dates and text#

Swap the test to colour rows by other kinds of values. All of these assume the selection starts at row 2:

Comparisons with text aren't case-sensitive, so "late" matches "Late". Dates must be real dates for <TODAY() to work. For a full walk-through of deadline rules, read how to highlight overdue dates.

The last rule is handy for reports: type a customer name in H1, and their rows light up. H1 has $ on both parts because every cell must look at the same input cell. A named range such as Pick makes it even clearer: =$B2=Pick.

How to highlight rows with two conditions#

Use AND when both tests must be true, and OR when either is enough:

• Late and 1,000 or more: =AND($D2="Late",$E2>=1000) colours only order 1006.
• Late or 1,500 or more: =OR($D2="Late",$E2>=1500) colours orders 1002, 1003, 1004, 1005, 1006 and 1008.

Lock the column in every reference inside AND and OR. One missing $ breaks that part of the test for cells in other columns.

Use different colours for different values#

To colour Late rows red, Pending rows yellow and Shipped rows green, make three rules on the same range:

1. =$D2="Late" with a red fill.
2. =$D2="Pending" with a yellow fill.
3. =$D2="Shipped" with a green fill.

Open Home > Conditional Formatting > Manage Rules to see them together. If two rules can match the same row, the one higher in the list wins. Use the arrow buttons to change the order.

Make the highlighting cover new rows#

A rule applies only to the range in Applies to. If new orders go into row 10 and below, they won't be coloured. Two fixes:

• Edit the range. In Manage Rules, change Applies to to something like =$A$2:$E$500.
• Use an Excel table. Click in the data and press Ctrl+T (Mac: ⌘T). Rules on a table's rows grow with the table as you add rows.

You can also select whole rows by clicking the row numbers, such as rows 2 to 500, and use the same =$D2="Late" formula. The rule then colours the row right across the sheet.

Why isn't my row highlighting working?#

Check these causes, most common first:

1. Only one column selected. The rule can only colour the cells it applies to. Select all the columns.
2. Missing $ before the column. Use =$D2, not =D2.
3. Formula written for the wrong row. If you selected A1:E9 including the header but wrote =$D2, every row is shifted by one. Match the formula to the first selected row.
4. Extra spaces in the data. "Late " with a trailing space doesn't equal "Late". Clean the column with TRIM, or use the SEARCH version.
5. Another rule wins. A rule higher in Manage Rules may set a different fill.

The same row technique also works for repeated values. See how to highlight duplicate rows. For more formula rules, read conditional formatting with a formula.

Practise it#

Colouring a whole row with a formula rule is a classic interview task, and the $ sign is what it really tests. Practise conditional formatting in a live spreadsheet, including whole-row rules with a $ on the column. Then practise absolute and mixed references to make the $ second nature.