How to reconcile invoices in Excel

Use separate Excel tables for Invoices, Payments, Applications, and Exceptions. Do not put all four concepts into one wide sheet. Assign stable identifiers and preserve the original exported values.

The Applications table should contain PaymentID, InvoiceID, AppliedAmount, Status, Reviewer, and ReviewedAt. Multiple rows naturally support one payment to several invoices and several payments to one invoice.

Create normalized copies of customer names and references. Typical cleaning includes trimming whitespace, standardizing case, and removing clearly irrelevant punctuation. Keep the original text beside the helper value.

Convert currency values to fixed decimal numbers and dates to actual Excel dates. Flag rows that remain text or produce an error instead of wrapping every formula in IFERROR and hiding the problem.

Use XLOOKUP, FILTER, or Power Query to identify candidate invoices with the same amount and currency. Add customer and reference checks so a common amount does not create a false match.

Return a review status when several invoices fit. A lookup that selects the first result is not a reliable reconciliation method.

For a small invoice list, test combinations manually or use Solver with binary selection cells. Always narrow the list to one customer and currency first. For partials, create an application row for the amount paid and calculate the remaining invoice balance from confirmed applications.

Avoid circular formulas that use a result to define its own source range. Recalculate control totals after adding rows or refreshing Power Query.

Protect formula columns, use data validation for statuses, add duplicate ID checks, and display control totals for imported, applied, unapplied, and outstanding amounts. Archive a reviewed copy for each period and document the workbook owner.

When source formats, volume, reviewers, or exception complexity grow, compare the effort of maintaining these controls with a dedicated workflow.