Highlight an Entire Row Based on a Cell Value (Excel and Google Sheets)
Highlighting one cell is easy. Highlighting the whole row, so a finished task turns green or an overdue invoice turns red right across the sheet, is where most people get stuck, because the built-in rules only colour the cell they test. The fix is a custom formula with one carefully placed dollar sign. Once you understand that dollar sign, every rule below takes seconds.
The one rule to remember
Put a $ before the column letter and no $ before the row number, and write the formula for the first row of your data.
For example, =$E2="Done" means: "for each row, look at column E in that same row". The $ locks the column, so every cell in the row (A, B, C, D...) checks column E. The row number is left free, so row 3 checks E3, row 4 checks E4 and so on.
How to do it in Excel
- Select the whole data range without headers, for example A2:F200. Start the selection in row 2 so the active cell is in the first data row.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Type the formula, for example
=$E2="Done". - Click Format, choose a fill colour and click OK twice.
How to do it in Google Sheets
- Select the range, for example A2:F200.
- Go to Format > Conditional formatting.
- Under Format cells if, choose Custom formula is.
- Type the formula, for example
=$E2="Done". - Pick the formatting style and click Done.
Google Sheets accepts the same formulas as Excel for everything on this page.
12 ready-to-use row rules
All examples assume your data starts in row 2. If it starts in row 5, change every 2 to 5.
| Highlight the row when... | Custom formula (data starts in row 2) |
|---|---|
| Status in column E is Done | =$E2="Done" |
| Amount in D is over 1000 | =$D2>1000 |
| Due date in C has passed and the task is not done | =AND($C2<TODAY(),$E2<>"Done") |
| Due in the next 7 days | =AND($C2>=TODAY(),$C2<=TODAY()+7) |
| Column B is blank | =$B2="" |
| Column B contains the word urgent (any case) | =ISNUMBER(SEARCH("urgent",$B2)) |
| Status is Late or Blocked | =OR($E2="Late",$E2="Blocked") |
| The date in A is a Saturday or Sunday | =WEEKDAY($A2,2)>5 |
| The value in A appears more than once | =COUNTIF($A$2:$A$500,$A2)>1 |
| The row has the highest sales in D | =$D2=MAX($D$2:$D$500) |
| Every other row (banding) | =MOD(ROW(),2)=0 |
| The name in A matches the name typed in H1 | =$A2=$H$1 |
Text comparisons such as =$E2="Done" are not case-sensitive in either program, so "done" and "DONE" also match.
Combining several colours
You can add several rules to the same range, for example green for Done, amber for due this week and red for overdue. Two things to know:
- Order matters. In Excel, open Conditional Formatting > Manage Rules and use the arrows to move the most important rule to the top. If two rules apply, the top one wins where they set the same thing, such as fill colour. Tick Stop If True to stop lower rules applying at all.
- In Google Sheets, rules are applied in the order listed in the sidebar; drag a rule up to give it priority.
A good order for a task list is: overdue (red) at the top, then due soon (amber), then done (green or grey).
Highlighting a row from a drop-down choice
If column E has a status drop-down (Data Validation in Excel, Insert > Drop-down in Sheets; see how to create a drop-down list), the row rules work as soon as someone picks an option. This is the simplest way to build a colour-coded tracker, from a project list to a Christmas budget tracker.
Highlight rows that match a search box
Type a name into H1 and use =$A2=$H$1 to light up every matching row. Note the difference: $A2 moves down the rows, but $H$1 is fully locked, because every row must compare with the same search cell. For a partial match, use =AND($H$1<>"", ISNUMBER(SEARCH($H$1, $A2))); the first part stops every row lighting up when H1 is empty.
Why is it highlighting the wrong rows?
- The formula refers to the wrong starting row. If your range starts at row 2 but the formula says
$E1, every highlight appears one row too high. The formula must point at the first row of the "Applies to" range. - The $ is missing or in the wrong place.
=E2="Done"without the $ makes each column check a different column, so only part of the row is coloured.=$E$2="Done"locks everything, so every row copies the result of row 2. - Numbers stored as text. If
=$D2>1000does nothing, the amounts may be text. Look for left-aligned numbers or a green triangle, and convert them with VALUE or Data > Text to Columns. - Dates stored as text. Date rules need real dates. Check with
=ISNUMBER(C2), which returns TRUE for a real date. Our guide to days between dates covers more date handling. - Extra spaces. "Done " with a trailing space does not equal "Done". Use
=TRIM($E2)="Done", or clean the data. - Blank rows get coloured. A rule such as
=$C2<TODAY()treats an empty cell as zero, which is an old date, so empty rows turn red. Add a check:=AND($C2<>"", $C2<TODAY()). - Quotation marks. Formulas copied from websites or documents sometimes contain curly quotes. Retype them as straight quotes in the formula box.
Make rules grow with your data
Apply the rule to a generous range such as A2:F1000 so new rows pick it up. In Excel, a better option is to convert the range to a table (Ctrl+T); conditional formatting applied to a table column expands automatically as you add rows. In Google Sheets you can use an open-ended range such as A2:F.
Copying rules to another sheet
In Excel, use the Format Painter on the formatted range, or copy and choose Paste Special > Formats. In Google Sheets, copy and use Paste special > Format only. Check the "Applies to" range afterwards, because pasted rules sometimes split into several pieces.
For more condition logic you can reuse inside these rules, see IF, AND, OR and IFS, and to colour repeated values only, see how to highlight duplicates.