An invoice tracker spreadsheet lists every invoice you send with its client, amount and due date, then marks it paid when the matching deposit shows on your bank statement. Two formulas do the rest: one counts days late, the other shows the balance still owed. You see at a glance who owes you and for how long.
What columns does an invoice tracker spreadsheet need?
An invoice tracker spreadsheet needs nine columns: invoice number, client, date sent, due date, amount, amount paid, date paid, days late and balance owed. The first five you type when you send the invoice. The paid columns come from your bank statement. The last two are formulas, so you never type them.
A sample tracker with five example invoices, as it would look on May 20, 2026:
| Invoice | Client (example) | Sent | Due | Amount | Paid | Date paid | Days late | Owed |
|---|---|---|---|---|---|---|---|---|
| 1041 | Harbor Cafe | 2026-03-02 | 2026-04-01 | 900.00 | 900.00 | 2026-03-28 | 0 | 0.00 |
| 1042 | Linden Dental | 2026-03-15 | 2026-04-14 | 1,250.00 | 1,250.00 | 2026-04-22 | 0 | 0.00 |
| 1043 | Harbor Cafe | 2026-04-01 | 2026-05-01 | 900.00 | 0.00 | 19 | 900.00 | |
| 1044 | North Yard Builders | 2026-04-10 | 2026-05-10 | 2,400.00 | 1,200.00 | 2026-05-12 | 10 | 1,200.00 |
| 1045 | Linden Dental | 2026-05-05 | 2026-06-04 | 1,250.00 | 0.00 | 0 | 1,250.00 |
The table shows the three states an invoice can be in. 1041 and 1042 are settled, so days late reads 0 even though 1042 arrived late. 1043 is unpaid and overdue. 1044 is part paid and overdue. 1045 is open and not yet due. The total owed, $3,350.00, is your accounts receivable on that date.
Add a status column if you like words better than numbers: "Paid", "Part paid", "Overdue" or "Open". It can be a formula too.
Marking invoices paid by matching bank deposits
Marking invoices paid means finding each client payment on your bank statement and copying its amount and date into the tracker. The statement is the proof the money arrived, so the tracker never says "paid" on a promise. Do it once a week, or once a month when the statement closes.
The quickest way is to put the statement's deposits in a spreadsheet beside the tracker. Sort the deposits by amount and the open invoices by amount, and most matches line up straight away. Use the deposit description to confirm the client: transfers usually carry the payer's name, and card processor payouts carry the processor's name instead.
A few rules keep the matching honest:
- Copy the deposit date, not the date the client said they paid. Under cash basis bookkeeping, income counts when the money arrives.
- Never mark paid on a pending deposit. Wait until it posts.
- Leave a note on any deposit you cannot match. An unmatched deposit is either an invoice you forgot to log or money that is not income at all, such as a refund or a transfer from savings.
If your bank gives only PDF statements, the deposits have to be typed or converted first. CheapBankConvert turns a PDF statement into a CSV or Excel file you can sort, with a single payment per statement, and it is in early access. The CheapBankConvert homepage explains the free preview.
Invoice tracker formulas for days late and amount owed
Two formulas carry the invoice tracker. Balance owed subtracts the amount paid from the invoice amount. Days late counts the days since the due date, but only while money is still owed, and shows zero when the invoice is settled or not yet due. Both copy down the whole column.
Assuming due date is in column D, amount in E, paid in F, days late in H and owed in I, row 2 holds:
I2: =E2-F2
H2: =IF(I2<=0, 0, MAX(0, TODAY()-D2))
The days late formula reads: if nothing is owed, show 0; otherwise show how many days have passed since the due date, never less than 0. Format column H as a number, not a date, or Excel will show a strange date in 1900.
For a status column, one nested IF covers all four states:
J2: =IF(I2<=0, "Paid", IF(H2>0, IF(F2>0, "Part paid, overdue", "Overdue"), "Open"))
Then add conditional formatting to column H: a fill colour for anything over 0, and a stronger one for anything over 30. Late payers stand out every time you open the file. A total at the bottom, =SUM(I2:I200), gives what clients owe you in all.
What if one deposit pays several invoices in the tracker?
When one deposit pays several invoices, split it across them in the tracker: enter each invoice's share in its own paid column, with the same date, until the shares add up to the deposit. When a deposit pays only part of an invoice, enter what arrived and let the balance owed column show the rest.
Clients who pay several invoices at once often send a remittance note listing them. Keep it with the statement. Without one, apply the money to the oldest invoices first, and tell the client that is what you did, so you agree on what is still open.
Watch for deposits that are slightly short. A client who pays $2,385.00 against an invoice of $2,400.00 may have deducted a transfer fee. Record the $2,385.00 and decide whether to chase the $15.00 or write it off. Either way, the tracker should show the decision, not a mystery balance.
Combined and partial payments are also where tracker errors start. If the shares you entered do not add up to the deposit on the statement, stop and fix it before moving on.
When an invoice tracker spreadsheet is no longer enough
An invoice tracker spreadsheet is no longer enough when you send more invoices than you can match in a short weekly session, when several people need to update it, or when you want reminders sent automatically. At that point, invoicing built into bookkeeping software, or a bookkeeper, saves more time than it costs.
Signs you have outgrown it:
- Matching takes over half an hour a week.
- Payments arrive through several processors, each paying out in batches that cover many invoices.
- You keep finding unmatched deposits weeks after they arrived.
- Chasing late payers takes more time than the work itself.
If the books as a whole feel like too much, when to hire a bookkeeper instead of doing it yourself sets out where that line usually falls. If you are still keeping the books yourself, the tracker fits into the monthly routine in DIY bookkeeping for small business on a tight budget.
A tidy tracker is useful outside the business too. When someone asks you to document your income, such as in a prenup financial disclosure, invoices matched to bank deposits make a clear, checkable record of what you earned.