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:

In Excel, it's Conditional Formatting → New Rule → Use a formula to determine which cells to format.

Steps

  1. 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.

  2. Order 5004 has missed its slot. Change its status in D5 to Late.

  3. A second rule, for big orders. Select A2:D9 again, choose Use a formula, and highlight in Green the rows whose amount is at least the figure in G1: =$C2>=$G$1.

  4. Because the rule reads G1, the threshold is a setting, not something buried in a rule. Change G1 to 1000.

Remember

Previous: Data bars and colour scales · Next: Sort a table