Tracking Invoices and Payments in Excel
Sending the invoice is the easy half. The half that costs small businesses real money is what happens after: which invoices are paid, which are partly paid, which are overdue and by how long, and what the total outstanding actually is today. Most people tracking this in Excel are doing it in a second spreadsheet, updated by hand, that disagrees with reality within a month.
Why the second spreadsheet always fails
The standard setup is a folder of invoice files plus a tracker sheet listing them — number, customer, amount, a column for “paid.” It fails for one structural reason: the tracker and the invoices are not connected. Every payment has to be recorded twice, once against reality and once in the tracker, and any time those two drift apart the tracker silently becomes fiction. A partial payment makes it worse, because now the tracker needs a paid amount and a balance, maintained by hand, per invoice, forever.
The fix is not a better tracker. It is making the invoices and the tracking the same system, so a payment recorded once updates everything that depends on it.
What connected tracking looks like
In EG Quote & Invoice, every document lives in one workbook, and the Home dashboard is a live view of all of them. This is the shape of it:
| Number | Client | Date | Status | Total | Balance | Due date |
|---|---|---|---|---|---|---|
| INV-200114 | Bayside Marine Supply | 07/24 | Finalized | 4,479.36 | 4,479.36 | 08/23 |
| INV-200113 | Harborlight Property Group | 07/06 | Partially Paid | 2,688.90 | 1,688.90 | 08/05 |
| INV-200112 | Coastal Print Works | 06/20 | Partially Paid | 1,201.21 | 601.21 | 07/20 · 14d |
| INV-200111 | Sunrise Dental Group | 06/18 | Finalized | 716.10 | 716.10 | 07/03 · 31d |
| INV-200110 | Apex Auto Body | 06/24 | Paid | 1,367.64 | 0.00 | 07/24 |
The document list on the Home dashboard. Status and balance are computed from recorded payments — nobody maintains this by hand.
Three things make this work where the tracker spreadsheet does not.
Status follows the money automatically. Record a payment against an invoice — full or partial, with the date, method, and reference — and the document moves through draft, finalized, partially paid, and paid on its own. A deposit recorded today changes the balance today. There is no second place to update.
Overdue is computed, not noticed. Every invoice carries a due date, and past-due documents show the date and the days late in red, on the list, without anyone checking a calendar. The two invoices above at 14 and 31 days late are the ones that need a phone call — and they surface themselves.
The totals are always current. Across the top of the dashboard: open quotes and their value, unpaid invoice count, and total outstanding. Filter the list — unpaid only, one client, overdue only — and the filtered totals recalculate for exactly what you are looking at. “What is Harborlight’s balance across everything?” is a two-click question.
Partial payments, deposits, and the balance problem
Deposits are where hand-maintained trackers really break, because one invoice now has a payment history rather than a paid flag. Here the history lives on the invoice itself: each payment with its date and reference, the running paid total, and the balance due — and the printed document shows Total, Amount Paid, and Balance Due, so the customer sees the same numbers you do. A progress-billed job with a deposit and two payments is just an invoice with three entries on it, not a reconciliation project.
When someone is late
The dashboard tells you who to chase; the reminder is one more click. The free edition shows overdue status and days late on every document. The Pro edition adds one-click payment reminders — a pre-filled email for any late invoice — but the tracking itself, the part that tells you who owes what, is entirely free.
Worth saying explicitly: none of this uses formulas you maintain. There is no VLOOKUP to a tracker sheet, no conditional formatting to keep alive, nothing that breaks when a row is inserted. It is an application that happens to run in Excel — Windows desktop Excel, 32-bit or 64-bit.
The tracking is in the free edition
Unlimited invoices, payment recording, balances, overdue tracking, and the dashboard — free, no account, no expiry. Load the sample data to see a working document list before entering anything of your own.
See EG Quote & InvoiceFree edition · Windows Excel · 32-bit and 64-bit
More on invoicing in Excel: all guides · the free quote & invoice software · customer and product databases · offline and subscription-free