Course / Conditional formatting
Highlight a whole row with a formula
Write your own test with a formula, and use $ so one cell decides the colour of its whole row. Intermediate · 5 min · Formula rule, Mixed references, Whole rows
The built-in rules test a cell against itself. Often the colour should depend on another cell: mark the whole order when its status says Late.
For that, you write the test as a formula. The rule highlights wherever the formula is true.
Two things make it work:
- Write the formula for the first selected cell. The sheet copies it to the others, shifting the references the way a copied formula shifts.
- Put a $ before the column letter you want every cell in the row to look at. In
=$D2="Late", the column is held on D while the row is free to move, so every cell in row 5 asks about D5.
In Excel, it's Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Steps
Mark every late order, across the whole row. Select the table without its headings,
A2:D9, click Conditional formatting in the toolbar, choose Use a formula, type=$D2="Late", and apply it in Red.Order 5004 has missed its slot. Change its status in
D5to Late.A second rule, for big orders. Select
A2:D9again, choose Use a formula, and highlight in Green the rows whose amount is at least the figure inG1:=$C2>=$G$1.Because the rule reads
G1, the threshold is a setting, not something buried in a rule. ChangeG1to 1000.
Remember
- A formula rule highlights wherever the formula is true.
- Write it for the first selected cell; it is copied to the rest.
- $ before the column (
$D2) makes a whole row follow one cell. - Point at a cell for the threshold (
$G$1), so it can be changed without touching the rule.
Previous: Data bars and colour scales · Next: Sort a table