An invoice tracker spreadsheet answers one question that your invoicing app, inbox and memory all answer badly: who owes me money right now, and how late are they?
When you send five invoices a month, you can keep it in your head. At fifteen, with a couple of part-payments and one client who always pays “next week”, you can’t. I once found a 1,200 invoice that was 74 days overdue because it had slid off the first page of my email. Nobody was being dishonest. I’d just stopped asking.
Here’s how to build a tracker that catches that, the formulas that do the work, and a follow-up routine that gets you paid faster without awkward conversations.
The columns your invoice tracker spreadsheet needs
Keep it to one row per invoice. These columns cover nearly every small business:
- Invoice number. Sequential, no gaps. INV-0041, INV-0042.
- Client. From a dropdown so names stay consistent.
- Issue date and due date. Due date can be a formula: issue date + 14 or + 30.
- Amount.
- Amount paid. Not a tick box. Part payments are common and a tick box can’t show them.
- Balance. Amount minus amount paid.
- Status. Calculated, never typed.
- Days overdue. Calculated.
- Last reminder sent. A date. You’ll thank yourself later.
Let formulas set the status
Typing “Paid” or “Overdue” by hand is how trackers go stale. Let the sheet decide. Assuming Amount is in E, Amount Paid in F and Due Date in D:
=IF(F2>=E2,”Paid”,IF(F2>0,”Part paid”,IF(TODAY()>D2,”Overdue”,”Open”)))
Then days overdue:
=IF(AND(F2<E2,TODAY()>D2),TODAY()-D2,””)
Add conditional formatting: red for Overdue, amber for Part paid, green for Paid. Now a glance tells you where to look.
One more useful status is “Due soon”. Flag anything due in the next 7 days with D2-TODAY()<=7. A friendly heads-up before the due date works better than any reminder after it.
Add a summary at the top
Four numbers, worked out with SUMIFS and COUNTIFS:
- Total outstanding (sum of Balance)
- Total overdue (sum of Balance where Status is Overdue)
- Number of overdue invoices
- Average days to get paid, for invoices already settled
That last one is underrated. If your terms are 14 days and clients average 31, you’ve learned something about your cash flow and maybe your terms.
Age your receivables
Group what’s owed into buckets: not yet due, 1 to 30 days late, 31 to 60, 61 to 90, and over 90. The older an invoice gets, the less likely it is to be paid in full. The over-90 bucket needs a phone call, not another email.
A follow-up routine that works
A tracker only helps if it drives action. Here’s a simple schedule that keeps things polite:
- 3 days before due: a friendly reminder with the invoice attached.
- On the due date: “Just a note that this is due today.”
- 7 days late: a firmer email with the amount and days overdue.
- 14 days late: phone or message. People ignore emails; they rarely ignore a call.
- 30 days late: pause further work and mention your late payment terms.
Pick one morning a week (Monday works) to sort the tracker by days overdue and send what’s needed. Ten minutes. Log the reminder date each time.
Get paid faster before you even chase
- Ask for a deposit on larger jobs. 30 to 50 percent is common for freelancers.
- Put the due date, not just the terms, on the invoice. “Due 28 November” is clearer than “Net 14”.
- Make paying easy. A payment link or bank details at the top, not buried in the footer.
- Invoice the day the work is done. Every day you wait is a day added to the end.
Mistakes that make trackers useless
- Deleting paid rows. Keep them. They feed your average days to pay and give you a record when a client says “I’m sure we paid that”.
- One tab per client. It feels tidy, but you can’t see total overdue across clients without adding up ten tabs. One list, filtered, is better.
- Mixing currencies in one column. If you bill in two currencies, add a currency column and a converted total, or the summary numbers will lie to you.
- Forgetting credit notes. A refund or discount after invoicing should be its own row with a negative amount, linked to the original invoice number.
- Updating it “later”. Log a payment the day it lands in your bank. Otherwise you’ll chase someone who already paid, which is a little embarrassing for everyone.
Templates that include invoice tracking
A standalone tracker is fine, but invoices are only half the picture. You also want paid invoices to land in your income and profit figures without retyping. That’s why I’d keep invoice tracking inside your bookkeeping file.
Clarity Books Easy Bookkeeping is a 13-tab spreadsheet for small businesses and freelancers. Its Invoices tab sets an automatic Paid, Unpaid or Overdue status, and it sits alongside income and expense logs, live bank and cash balances, monthly and annual dashboards, and an automatic Profit & Loss, Balance Sheet, Cash Flow and Tax Summary with quarterly estimates. It works in Google Sheets and Excel and comes with a PDF guide.
If you want more detail on late payers, Sorbet Books goes a step further on invoices: Paid, Part paid, Open, Due soon and Overdue statuses, days late and your average days to get paid. It’s also a colourful, multi-year bookkeeping file with a budget planner and a five-year comparison.
Short version: Clarity Books for clean, simple books with invoices included. Sorbet Books if you want part-payment tracking and the payment-speed numbers.
FAQ
How do I track unpaid invoices in Excel?
List each invoice on its own row with amount, amount paid and due date. Use a formula to set the status and another to count days overdue, then sort or filter by status each week.
Is a spreadsheet good enough, or do I need invoicing software?
For under about 30 invoices a month, a spreadsheet works well. Software adds automatic reminders and online payments, which start to matter at higher volumes.
Should I charge late fees?
You can if it’s in your terms before the work starts. Check local rules, since some places limit late fees. Many freelancers use them more as a reason to be paid on time than as income.
Can an invoice tracker spreadsheet handle part payments?
Yes, as long as you record amount paid rather than a Paid tick box. The balance column then shows what’s still owed.