Conditional formatting changes how a cell looks based on its content: red if a value is negative, green if a target is met. The built-in rules (greater than, between, top ten, data bars) are enough for simple cases. When you want to colour a whole row based on a single column, though, or compare values across different columns, you need the formula-based option. That is where conditional formatting becomes really useful.
In Excel you find it under Home, Conditional Formatting, New Rule, "Use a formula to determine which cells to format". In LibreOffice Calc under Format, Conditional, with the condition "Formula is". In Google Sheets under Format, Conditional formatting, choosing "Custom formula is".
How the program thinks
A rule's formula must return TRUE or FALSE. The program evaluates it for every cell in the selected range, adjusting the references exactly as if it were copying the formula into each cell. When the result is TRUE, it applies the format.
This leads to the golden rule: write the formula with the top-left cell of the selection in mind. If you select A2:F200, the formula must be correct for A2; the program takes care of the rest.
Highlighting whole rows
Suppose you have a list of invoices in A2:F200, with the status in column E. You want to colour the whole row when the status is "Unpaid".
- Select A2:F200, starting from A2.
- Create a formula rule:
=$E2="Unpaid". - Choose the format, for example a light red fill.
The dollar sign before the E is the key point. It locks the column, so every cell in the row, from A to F, always looks at column E. The row number stays relative instead, so row 3 looks at E3, row 4 at E4 and so on. Without the dollar sign, cell B2 would look at F2, cell C2 at G2, and the rule would only work in the first column.
Useful examples
Due dates
With the due date in D and a status column in E:
- overdue and unpaid:
=AND($D2<TODAY(), $E2<>"Paid"); - due in the next seven days:
=AND($D2>=TODAY(), $D2<=TODAY()+7).
Because TODAY() updates every day, the sheet recolours itself with no intervention.
Duplicates across several columns
The built-in duplicates rule considers a single column. To flag rows where the combination of name and date of birth repeats:
=COUNTIFS($A$2:$A$500, $A2, $B$2:$B$500, $B2)>1
Banded rows
For easier reading of long tables, every other row: =MOD(ROW(), 2)=0. Unlike a fill applied by hand, it stays correct even when you sort or filter the data.
Comparing two columns
To highlight values in column B that differ from column C on the same row: =$B2<>$C2. It is a quick way to check two versions of a price list side by side.
Value missing from another list
To flag codes in A that do not appear in a list on another sheet:
=COUNTIF(Customers!$A:$A, $A2)=0
In some Excel versions rules do not accept direct references to other sheets; in that case you name the range and use the name in the formula.
The order of the rules
A cell can be subject to several rules. The program evaluates them in the order they appear in the rules manager and, if two rules set the same attribute (for example the fill colour), the one higher up wins. Excel also offers the "Stop If True" option, which skips the rules that follow.
A sensible order goes from most to least serious: first "overdue", then "due soon", then the banded rows.
Frequent mistakes
- A selection that does not start on the formula's row. If you select from A1 but write the formula for row 2, everything ends up shifted by one row.
- Dollar sign in the wrong place.
=E$2="Unpaid"locks the row instead of the column: every row looks at E2. - Quotes and capitals. The comparison
="Unpaid"is not case sensitive, but it is sensitive to spaces: "Unpaid " with a trailing space is not recognised. - Too many overlapping rules. Copying and pasting formatted cells multiplies the rules and slows down large files. Open the rules manager now and then and consolidate.
Do not overdo the colours
In my work I use conditional formatting to draw attention to what requires action, not to decorate. Two or three colours with a clear meaning work better than a rainbow: if everything is highlighted, nothing really is. Remember too that not everyone tells red from green easily: pairing the colour with an icon or bold text makes the message readable for anyone.
In short
- A formula rule must return TRUE or FALSE and is written for the first cell of the selection.
- To colour whole rows, lock the control column with the dollar sign:
=$E2="Unpaid". - TODAY() makes due-date rules dynamic.
- COUNTIF and COUNTIFS let you flag duplicates and missing values.
- Check the order of the rules and use a few colours with a precise meaning.
