WorkluneHow to Track Invoices and Payments in a Spreadsheet
An invoice tracker becomes much more useful when it connects billing with actual payments. Instead of only listing invoices, track what has been paid, what remains outstanding and what needs follow-up.
Create an invoice register
Give every invoice a unique ID and record the client, issue date, due date and original invoice amount. Preserve the original invoice amount even after payments arrive.
A stable invoice ID is the link between the invoice and each payment. This makes partial payments easy to handle without overwriting history.
Log payments separately
Use a payment log with payment ID, invoice ID, client, date, amount, method and reference. If a client pays in two installments, enter two payment rows.
Paid amount can then be calculated as the sum of all payments linked to the invoice. Outstanding balance is invoice amount minus paid amount.
Calculate payment status
Useful statuses include Unpaid, Part Paid, Paid and Overdue. The status should depend on outstanding balance, whether any payment has been received and whether the due date has passed.
Automatic status rules reduce manual cleanup and make dashboards more reliable.
Measure collection performance
Compare total invoiced with total collected, and review collection rate over time. Also track open invoice count and total overdue value.
Do not judge the business only on revenue billed. Strong sales can still create cash pressure if payments arrive slowly.
Use a weekly receivables routine
Once or twice a week, review invoices due soon, invoices newly overdue and the oldest outstanding balances. Follow up according to your payment terms and client relationship.
A predictable routine is usually more effective than waiting until cash is urgently needed before checking who owes you money.