Invoice tracker in Excel or Google Sheets: set it up once, get paid faster
By the BizDocs Studio team · Updated
- •An invoice tracker lists every invoice with client, dates, amount, payments and balance, and calculates status and days overdue.
- •Record payments separately so partial payments reconcile.
- •Aging buckets (current, 1–30, 31–60, 61–90, 90+ days) show where cash is stuck.
- •Review a chase list of overdue invoices weekly and send statements monthly.
Invoicing software sends invoices; an invoice tracker makes sure they are paid. If you invoice from Word, a PDF tool or a platform that does not chase, a spreadsheet tracker is the missing half.
Columns to include
| Column | Purpose |
|---|---|
| Invoice # | Unique reference |
| Client | Dropdown from a client list |
| Invoice date and terms | Calculates due date |
| Amount and tax | Total invoiced |
| Paid to date | Sum of payments for this invoice |
| Balance | Total − paid |
| Status | Paid, open, partial, overdue |
| Days overdue | Today − due date when unpaid |
Log payments separately
Keep a payments list (date, invoice #, amount, method). The tracker sums payments by invoice number, so partial payments and overpayments reconcile automatically and you have an audit trail.
Aging and the chase list
Group open balances into aging buckets. Then rank overdue invoices by days late with the client's contact details. A weekly ten-minute pass through that list is the single most effective collections habit for a small business.
Every invoice and payment in one place. Know who owes you, and how late.
Client statements
Once a month, send each client with an open balance a statement listing their invoices, payments and total due. Our invoice tracker generates statements and the chase list from the same data, in Excel or Google Sheets.
Formulas behind the columns
Due date = invoice date + payment terms (days)
Paid to date = SUMIFS(payments amount, payments invoice #, this invoice #)
Balance = total − paid to date
Status = paid if balance ≤ 0; overdue if today > due date; partial if paid > 0; otherwise open
Days overdue = today − due date when the balance is open
Collections script that works
- Day 1 after due: friendly reminder with the invoice attached
- Day 7: second reminder, ask if anything is holding payment
- Day 14: phone call, agree a payment date
- Day 30: final notice stating late fees or next steps per your terms
Most overdue invoices are paid after the first or second reminder. The tracker's job is to make sure those reminders actually go out.
Metrics to watch
- Days sales outstanding: average days from invoice to payment
- Overdue share: overdue balance ÷ total open balance
- Top five clients by open balance
- Average invoice size and monthly invoiced trend
Frequently asked questions
How do I track invoices in Excel?
List invoices with client, date, terms, amount, payments and balance; use formulas for due date, status and days overdue; and keep payments in a separate list summed by invoice number.
What is an aging report?
A summary of unpaid invoices grouped by how overdue they are, for example current, 1–30, 31–60, 61–90 and over 90 days.
Does an invoice tracker replace accounting software?
Not entirely. It tracks receivables; accounting software also handles expenses, bank reconciliation and payroll.
Every invoice and payment in one place. Know who owes you, and how late.