Invoice reconciliation in Excel: free samples and a worked example

Keep invoices and payments in separate Excel tables. Give each row a stable source identifier. Preserve original text columns, then add dedicated normalized fields for amount, date, customer, and reference. Avoid editing the imported source cells in place.

Create an applications table rather than writing a matched invoice number directly into the payment row. This supports one payment to several invoices and several payments to one invoice.

Spreadsheets allow almost any edit, which is both their strength and their weakness. Add protected formula columns, validation for statuses, a duplicate identifier check, versioned source files, and a reconciliation control total. Review formula ranges whenever rows are appended.

Do not use floating point equality for currency comparisons. Normalize to cents or use fixed-decimal values, and display the tolerance policy clearly.

Consider a dedicated workflow when multiple people edit the workbook, imports change every month, grouped payments are common, files span several clients, or reviewers cannot reconstruct who confirmed a match. The goal is not to ban spreadsheets. It is to stop asking one workbook to provide import validation, matching logic, collaboration, and audit history at once.

InvoiceReconcile accepts CSV and XLSX files so the transition can start with current exports rather than a required accounting integration.

Three open balances of $1,500, $1,225, and $2,000 total $4,725. A payment of $4,725 is a candidate combined match, but payer and remittance evidence still need review before allocation.

If the deposit is $4,690, the $35 difference should remain visible until supporting evidence explains whether it is a fee, short payment, credit, or the wrong invoice set. Do not silently write it off. InvoiceReconcile suggests relationships for review; it does not automatically post accounting decisions back to a ledger.