How to track unpaid invoices as a freelancer, in one spreadsheet
Most invoicing tools are good at making invoices and bad at showing what’s late. A spreadsheet can do the second job well in about ten columns. Here’s how to build one, and what to do once it shows an invoice is overdue.
The columns you need
One row per invoice:
| Column | Holds | Type |
|---|---|---|
| A | Invoice number | you type |
| B | Client | you type |
| C | Issue date | you type |
| D | Payment terms in days | you type, or look up per client |
| E | Due date | formula |
| F | Amount | you type |
| G | Paid date | you type when the money arrives |
| H | Status | formula |
| I | Days overdue | formula |
Keep typed columns and formula columns visually distinct, for example with a pale fill on the typed ones. It stops you overwriting a formula by accident months later.
The formulas
Due date is the issue date plus the payment terms:
=IF(OR(C5="",D5=""),"",C5+D5)Status should change on its own, without you touching the row:
=IF(A5="","",IF(G5<>"","Paid",IF(TODAY()>E5,"Overdue","Open")))The order matters. A paid invoice is paid, whatever the date, so the paid check comes first. TODAY() recalculates every time the file opens, so an invoice turns Overdue the day after its due date.
Days overdue only for overdue invoices:
=IF(H5="Overdue",TODAY()-E5,"")An ageing summary
Late payments get harder to collect the older they are. An ageing summary shows how much money sits in each age band. With amounts in column F and days overdue in column I:
| Band | Formula |
|---|---|
| Not yet due | =SUMIFS(F:F,H:H,"Open") |
| 1–30 days late | =SUMIFS(F:F,I:I,">=1",I:I,"<=30") |
| 31–60 days late | =SUMIFS(F:F,I:I,">=31",I:I,"<=60") |
| Over 60 days late | =SUMIFS(F:F,I:I,">60") |
Look at it once a week. Anything in the last two rows needs a phone call, not another email.
Don’t forget unbilled work
The other leak is work you did but never invoiced. If you log hours in the same file with a column for the invoice number they went on, unbilled work is every row without one:
=SUMIFS(Amount,InvoiceNo,"")A large number here at the end of a month is money you can collect simply by sending an invoice.
A chasing schedule that stays polite
- On the due date: a friendly note with the invoice attached and the payment details repeated.
- 7 days late: ask whether the invoice reached the right person, and whether anything is blocking payment.
- 14 days late: a firmer email with the amount, the days overdue and a date you expect payment by.
- 30 days late: call. Then follow up in writing with what was agreed.
Keep each message short and factual. Most late invoices are lost in someone’s inbox, not deliberately unpaid.
Know your rights on late payment
In the EU, business-to-business invoices fall under the Late Payment Directive (2011/7/EU). It lets you charge statutory interest on late payments and a fixed €40 compensation for recovery costs per invoice. The exact rules depend on your country’s law, so check the local version before you add charges. Even if you never use it, putting the payment terms and the late-payment clause on every invoice makes clients pay on time more often.