Colour the whole row when the invoice is paid

With regular conditional formatting, only the cell itself gets coloured. For an invoice list, task list or members list, you often want to colour the whole row when one column contains something, for example "Betaald". You do this with a rule using a formula.

Step by step

1. Select the whole table without headers, in the example A2:H13. So not just the Status column.
2. On the Home tab, click Conditional formatting and choose New rule.
3. For Rule, choose the type Formula.
4. Type the formula =$H2="Betaald". H is the column with the status, 2 is the first row of your selection.
5. For Format, choose a fill colour and click OK.

Why the dollar sign?

The dollar sign before the H locks the column: every cell in the row looks at column H. There's no dollar sign before the 2, so row 3 looks at H3, row 4 at H4, and so on. If you make it =$H$2, everything looks at the same cell and either colours everything or nothing.

More examples

• Overdue and still open: =AND($H2<>"Betaald",$F2<TODAY()-30): open invoices older than 30 days.
• Alternating colour: =MOD(ROW(),2)=0.
• A high amount: =$G2>200.

Pitfalls

• Everything or nothing coloured? Check the dollar signs and whether the formula starts at the first row of your selection.
• Copying and pasting rows can create duplicate rules. Clean them up via Conditional formatting, Manage rules (see Conditional formatting).
• Capital letters don't count in the comparison: "betaald" also gets coloured. A trailing space does: "Betaald " doesn't get coloured.