Chasing late payment is the least enjoyable part of running a business, and the easiest to forget. Conditional formatting fixes that by colouring your invoice list automatically: overdue invoices turn red, ones due soon turn amber, and paid ones drop out of view. In this guide I will show you the exact formulas to set this up so your spreadsheet nudges you instead of the other way round. It is one of those quiet wins that pays for itself the first time it stops an invoice slipping through the cracks.
Late payment is a genuine drag on UK small businesses. Cash that should be in your account sits in someone else’s, and the longer an invoice goes unpaid the harder it becomes to collect. The single most effective thing you can do is chase early and consistently, but consistency is hard when invoices are buried in a long list. Conditional formatting puts the urgent ones in front of you automatically, every single time you open the file, which is exactly the kind of prompt a busy owner needs.
Before we build the rules, it helps to understand what conditional formatting is. It is simply a way of telling Excel “if this cell, or this row, meets a condition, apply this format”. The condition is a formula that returns either true or false, and the format is whatever colour, fill or font you choose. Everything below is just different versions of that one idea.
Step 1: Build a simple invoice tracker
You only need a handful of columns. The key ones are the due date and the status, because those drive the colours.
| Invoice | Customer | Amount (£) | Due date | Status |
|---|---|---|---|---|
| 1041 | Acme Joinery | 1,440.00 | 30/04/2026 | Unpaid |
| 1042 | Bright Cafe | 780.00 | 15/05/2026 | Unpaid |
| 1043 | Harbour Ltd | 2,100.00 | 20/05/2026 | Paid |
Step 2: Highlight overdue invoices in red
Select your data rows, then go to Home > Conditional Formatting > New Rule and choose “Use a formula”. The trick is to compare the due date with today’s date and check the invoice is still unpaid. Assuming the due date is in column D and status in column E, with your first data row at row 2, use:
=AND($D2
Set the format to a red fill. The single dollar sign before the column letter locks the column but lets the row move, so the rule applies down the whole list. TODAY() updates every day the file is opened, so the colours stay current without any effort.
Step 3: Add an amber "due soon" warning
Add a second rule, above the red one in the rule list, for invoices due within the next seven days: =AND($D2>=TODAY(),$D2<=TODAY()+7,$E2="Unpaid"). Give it an amber fill. Now anything landing this week shows amber, and anything past due shows red, which is the priority order you want. The seven-day window is just a suggestion; if your customers typically need longer notice, widen it to fourteen days by changing the +7 to +14.
The reason this traffic-light approach works so well is that it matches how you actually think about money owed to you. Red is urgent and needs a firm chase today. Amber is a gentle heads-up that lets you send a friendly reminder before the due date even passes, which is often the most effective nudge of all. Green, or no colour, means you can leave it alone. Encoding that mental model into the spreadsheet means you spend your time acting on invoices rather than sorting through them.
Step 4: Fade out the paid invoices
A third rule keeps your eye on what still needs chasing. Use =$E2="Paid" with a soft grey font and no fill. Paid invoices quietly recede while the unpaid ones stand out. Excel applies rules from the top down, so make sure the paid rule sits where it will not override your red and amber rules; testing with a few sample rows confirms the order.
Step 4b: Keep the rules manageable
It is tempting to keep adding rules until your invoice list looks like a fruit salad, but restraint pays off. Three or four well-chosen colours communicate far more than a dozen competing ones. If you ever need to review or tidy your rules, go to Home > Conditional Formatting > Manage Rules, where you can see them all in order, edit their formulas, and drag them up or down to change priority. Set the "Applies to" range here as well, so a single rule covers your whole table rather than just the cells you happened to select. Spending two minutes in this dialog now saves confusion when your list grows and you cannot remember why a row is the colour it is.
Step 5: Add a days-overdue column
Numbers help as well as colours. In a spare column, work out how many days an invoice is overdue with =IF(E2="Paid","",MAX(0,TODAY()-D2)). The MAX keeps it from showing negative days for invoices not yet due, and the IF blanks it out once paid. Sort by this column and your worst offenders rise to the top, which is exactly the order you want to work through when you sit down to chase.
You can take this one step further with a polite, automatic reminder note. A formula such as =IF(AND(D2
If you raise a lot of invoices, add a summary cell at the top of the sheet that counts how many are overdue and totals how much they are worth. Use =COUNTIFS(D:D,"<"&TODAY(),E:E,"Unpaid") for the count and =SUMIFS(C:C,D:D,"<"&TODAY(),E:E,"Unpaid") for the value. Seeing "4 invoices overdue, £6,310 outstanding" the moment you open the file is a powerful motivator, and it gives you a single figure to watch as your chasing brings it down. That headline number turns a passive list into a target, which is often exactly the prompt a busy owner needs to pick up the phone.
This whole approach slots straight into our invoice tracker template, which already keeps the due dates and statuses in tidy columns. The beauty of building it on conditional formatting is that there is nothing to remember and nothing to run. Open the file any morning and the picture is current: red means chase now, amber means it is coming up, and everything else can wait. That small shift, from you watching the invoices to the invoices watching themselves, is what keeps cash flowing.
Common mistakes
- Anchoring the wrong way. Use
$D2, not$D$2, so the rule moves down the rows but stays on the right column. - Rule order. Excel stops at the first matching rule in some cases; put the most specific rules at the top.
- Dates stored as text. If
TODAY()comparisons do nothing, your dates are probably text. Reformat them as real dates. - Forgetting the status check. Without
$E2="Unpaid", paid invoices still turn red once their due date passes.
Frequently asked questions
Will the colours update automatically?
Yes, because TODAY() recalculates each time you open the file. An invoice that becomes overdue overnight will show red the next time you look.
Why is my conditional formatting not working?
The usual culprits are dates stored as text, the wrong anchoring on the cell reference, or rules in the wrong order. Check those three first.
Can I highlight whole rows rather than single cells?
Yes. Select all the columns in your data range before creating the rule, and use a column-locked reference like $D2. The rule then colours the entire row.
Does this work in Google Sheets?
It does. Google Sheets has conditional formatting with custom formulas too, and TODAY() works the same way.
