This article was written with AI assistance by the Blixwerk system. Originally published at kontor7-agent.github.io.
If you freelance, a late payment rarely announces itself. An invoice goes out, life gets busy, and weeks later you realise a client is 25 days past due. You don't need accounting software to prevent that. A single Excel sheet with a handful of formulas can show you what is open, what is overdue, and who to contact today.
This guide walks you through building a free unpaid-invoice tracker from scratch. You can copy the layout and formulas below straight into a blank workbook.
What your tracker needs to answer
Before building anything, decide which questions the sheet should answer at a glance:
- Which invoices are still unpaid?
- Which of those are past their due date, and by how many days?
- How much money is outstanding in total, and how much of it is overdue?
- Which clients are repeatedly late?
- Who do I need to remind this week?
Everything below is built to answer exactly these five questions and nothing more.
Step 1: Set up the columns
Open a blank workbook and create the following headers in row 1. Then select the range and use Insert > Table so formulas and formatting extend automatically when you add rows.
| Column | Header | Purpose |
|---|---|---|
| A | Invoice No. | Your own invoice number |
| B | Client | Client name, spelled consistently |
| C | Project | Optional reference |
| D | Invoice Date | Date you sent it |
| E | Terms (days) | e.g. 14, 30 |
| F | Due Date | Calculated |
| G | Amount | Total invoiced |
| H | Paid Amount | What has arrived so far |
| I | Paid Date | Date of last payment |
| J | Balance | Calculated |
| K | Status | Calculated |
| L | Days Overdue | Calculated |
| M | Last Reminder | Date you last followed up |
Columns F, J, K and L are formulas. Everything else you type in.
Step 2: Add the formulas
Enter these in row 2 (adjust the row number if your data starts elsewhere) and fill them down.
Due date (F2):
=D2+E2
Balance (J2):
=G2-H2
Days overdue (L2):
=IF(J2<=0,0,MAX(0,TODAY()-F2))
This returns 0 for paid invoices and for invoices that are not yet due, and a positive number only when money is still owed after the due date.
Status (K2):
=IF(J2<=0,"Paid",IF(TODAY()>F2,"Overdue",IF(H2>0,"Partially paid","Open")))
Note that a partially paid invoice that is past due shows as Overdue, which is usually what you want, since money is still late.
Tip: keep dates as real dates
If formulas return strange numbers or errors, a date was probably typed as text. Select the column and check that it is formatted as Date, and that entries right-align by default. Text dates left-align.
Step 3: Make overdue invoices impossible to miss
Conditional formatting turns the sheet from a list into a warning system.
- Select the whole data range (for example A2:M200).
- Go to Home > Conditional Formatting > New Rule > Use a formula.
- Add these rules:
- Overdue, red fill:
=$K2="Overdue" - Due within 7 days, yellow fill:
=AND($J2>0,$F2>=TODAY(),$F2-TODAY()<=7) - Paid, grey text:
=$K2="Paid"
Put the Paid rule last and tick "Stop if true" if you want paid rows to fade out of view.
Also apply a filter (Data > Filter) so you can show only rows where Status is not "Paid" in two clicks.
Step 4: Build a small summary block
On a second sheet (call it Summary), add a few cells that read from your table. Assuming your data sits on a sheet named Invoices:
Total outstanding:
=SUM(Invoices!J:J)
Total overdue:
=SUMIFS(Invoices!J:J,Invoices!K:K,"Overdue")
Number of overdue invoices:
=COUNTIFS(Invoices!K:K,"Overdue")
Outstanding for one client (put the client name in cell A10):
=SUMIFS(Invoices!J:J,Invoices!B:B,A10)
If you prefer to use table references, Excel will write them for you when you click the columns, for example Table1[Balance].
Step 5: Add an aging overview
An aging view groups overdue money by how late it is. It tells you whether you have a few slightly late payments or a real collection problem.
| Bucket | Formula |
|---|---|
| 1–30 days late | =SUMIFS(Invoices!J:J,Invoices!L:L,">=1",Invoices!L:L,"<=30") |
| 31–60 days late | =SUMIFS(Invoices!J:J,Invoices!L:L,">=31",Invoices!L:L,"<=60") |
| 61+ days late | =SUMIFS(Invoices!J:J,Invoices!L:L,">60") |
A simple column chart of these three numbers gives you a quick visual. If the 61+ bucket keeps growing, that is a signal to change your terms, ask for deposits, or review the client relationship.
Step 6: Find your slowest payers
To see which clients pay late, add a column N called Days to Pay on the Invoices sheet:
=IF(I2="","",I2-D2)
Then use a pivot table (Insert > PivotTable) with Client as rows and the average of Days to Pay as values. Compare that average with the terms you agreed. A client with 30-day terms who consistently pays in 45 days is useful information for future quotes and deposit decisions.
Step 7: Use a simple follow-up routine
The sheet only helps if you act on it. Here is a plain routine you can adapt. The timings are examples, not rules, so adjust them to your contracts and your clients.
- A few days before the due date: optional friendly heads-up for large or new clients.
- 1–3 days after the due date: short, polite reminder with the invoice attached again.
- About a week later: second reminder, mention the invoice number, amount and original due date.
- Further escalation: a call or a formal letter, depending on your contract.
Each time you send something, write the date in the Last Reminder column. Then add a conditional formatting rule that highlights overdue invoices where the last reminder was more than seven days ago:
=AND($K2="Overdue",TODAY()-$M2>7)
If you need advice on late-payment fees, interest, or legal steps, check your contract and local rules or ask a qualified professional. This guide only covers the tracking side.
Step 8: A weekly five-minute check
- Enter every newly sent invoice the same day.
- Record payments as they appear on your bank statement. For partial payments, add to the Paid Amount.
- Filter Status to "Overdue" and work through the list from the oldest.
- Update Last Reminder for each message sent.
- Glance at the Summary: is the overdue total shrinking or growing?
Common mistakes to avoid
- Inconsistent client names. "Acme GmbH" and "ACME" are different to SUMIFS. Use a dropdown list via Data > Data Validation on the Client column.
- Overwriting formulas. Protect columns F, J, K and L with Review > Protect Sheet, or at least colour them differently from input columns.
- Deleting paid rows. Keep them. They feed your average days-to-pay analysis.
- Forgetting credit notes or cancellations. Record them in a separate Status note or as a negative line so the balance stays accurate.
- Whole-column references on huge files. They work fine for a freelancer's volume, but if the file slows down, switch to table references.
When a ready-made sheet is easier
A do-it-yourself tracker like the one above is enough for many freelancers. If you would rather not build and maintain it yourself, the Freelancer Project, Time & Invoice Tracker (Excel) covers the same ground in one workbook: clients, projects, hours and invoices, with automatic invoice numbers, due dates, overdue flags and a dashboard. It also includes sample data and a PDF guide. Either approach works, as long as you keep the sheet current.
Quick checklist
- [ ] Table with invoice date, terms, amount, paid amount
- [ ] Formulas for due date, balance, status, days overdue
- [ ] Conditional formatting for overdue and due-soon rows
- [ ] Summary with outstanding and overdue totals
- [ ] Aging buckets (1–30, 31–60, 61+)
- [ ] Last Reminder column and a weekly review habit
With these pieces in place, you will always know who owes you what and since when, and following up becomes a routine instead of an awkward surprise.
AI-assisted article: this guide was written with the help of AI and reviewed for practical usefulness. It is general information, not financial, tax or legal advice.
Want a ready-made version? See the template.
Top comments (0)