How to Reconcile Vendor Statements with Your Invoices

Many suppliers send a monthly statement: a list of every invoice, credit memo and payment on your account, with a closing balance. It is tempting to file it unread, or to pay the balance at the bottom. Both are mistakes. The statement is the vendor's ledger, not yours, and the two drift apart more often than you would think.

Reconciling means matching each item on the statement to your own records and explaining every difference. Done monthly for your main vendors, it takes minutes and catches problems while they are still easy to fix.

Why the balances don't match

Almost every difference falls into one of these categories. Knowing them in advance makes the investigation much faster.

Step 1: Get both lists into Excel

You need two tables with the same basic columns: Invoice #, Date, Amount and a Type (Invoice, Credit, Payment). Put the vendor statement on a sheet named Statement and your records for that vendor on a sheet named Ours.

Your side usually comes from your AP spreadsheet or an accounting report, such as a vendor transaction or open bills report in QuickBooks, exported to Excel. The vendor's side is the hard part, because statements usually arrive as PDFs or paper. Run the statement through the PDF invoice to Excel converter, check the extracted rows in the preview, and export to Excel. If you need the underlying invoices too, the invoice to Excel converter handles those in the same pass.

Make sure amounts use the same sign convention on both sheets. A common choice: invoices positive, credits and payments negative. Then each sheet's total equals its balance.

Step 2: Match the statement to your records

On the Statement sheet, with the cleaned invoice number in column A and the amount in column C, add these columns:

D  Found in ours?      =COUNTIFS(Ours!$A:$A, A2)>0
E  Our amount          =XLOOKUP(A2, Ours!$A:$A, Ours!$C:$C, "MISSING")
F  Difference          =IF(ISNUMBER(E2), C2-E2, C2)
G  Status              =IF(NOT(D2), "Not in our books",
                          IF(ABS(F2)<0.01, "Match", "Amount differs"))

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. On older versions, use INDEX and MATCH instead: =IFERROR(INDEX(Ours!$C:$C, MATCH(A2, Ours!$A:$A, 0)), "MISSING").

Step 3: Match your records to the statement

Matching in one direction only finds half the problems. Repeat in reverse on the Ours sheet to find bills you recorded that the vendor does not show:

D  On statement?       =COUNTIFS(Statement!$A:$A, A2)>0
E  Entered twice?      =COUNTIFS($A:$A, A2)>1
F  Status              =IF(E2, "Possible duplicate",
                          IF(D2, "On statement", "Not on statement"))

Anything flagged "Possible duplicate" deserves attention first, because that is where real money leaks. "Not on statement" items are often payments you made after the statement cut-off date.

Step 4: Build the reconciliation summary

A reconciliation is complete when you can start from the vendor's balance, list every explained difference, and land exactly on your own balance. Lay it out like this on a Summary sheet:

Adjust signs to fit your convention, but the goal is fixed: line 7 should be zero. If it is not, something is still unexplained. Look for credits recorded on only one side, items dated in the wrong month, or a duplicate you have not yet flagged.

Step 5: Resolve and document

1. Missing invoices: ask the vendor for a copy. Before entering it, confirm you actually received the goods or service, ideally by checking the packing slip or receiving record.
2. Unapplied payments: email the vendor your payment date, amount, check number or ACH reference, and the invoices it covered.
3. Missing credits: request a credit memo number. Do not short-pay an invoice based on a verbal promise.
4. Amount differences: compare line items. Price changes, freight and sales tax are the usual culprits. Extracting line items, as described in how to extract line items from invoices, makes this a quick side-by-side comparison.
5. Duplicates: void or delete the duplicate in your records and make sure it has not been scheduled for payment.

Save the reconciliation workbook with the statement PDF, named by vendor and month. Your future self, your accountant and any auditor will thank you. Our guide on how long to keep receipts for taxes covers general retention timelines that apply to supporting documents like these as well.

How often should you reconcile?

Monthly is a sensible default for vendors you buy from regularly or owe significant amounts to. For occasional vendors, reconciling when a statement arrives, or at quarter end, is usually enough. Always reconcile before year-end close, before switching accounting systems, and any time a vendor sends a past-due notice you did not expect.

A worked example

Say Bay Lumber's October statement shows a closing balance of $4,860.00, but your records show $2,740.00 owed, a difference of $2,120.00. Running the formulas above turns up three items:

• Invoice 88577 for $1,450.00 is on the statement but not in your books. The packing slip shows the delivery arrived on October 29, so the invoice simply was never entered. Enter it.
• A $600.00 check you mailed on October 30 does not appear on the statement, dated October 31. It is a timing difference and should show up next month.
• Invoice 88412 is $70.00 higher on the statement than in your books. Comparing the lines shows a delivery fee that was added after the original quote. Decide whether you agreed to it, then either record it or dispute it.

Those three items explain the gap exactly: $1,450.00 missing from your books, plus the $600.00 payment the vendor has not yet recorded, plus the $70.00 fee, equals $2,120.00. With every dollar explained, the reconciliation is done, and you have two concrete actions to take.

No statement? Ask for one

Not every vendor sends statements automatically, but most will email one on request, or let you download an account history from their portal. For your largest suppliers, ask to receive one every month.

Make next month easier

Reconciliation is fastest when your own records are clean to begin with. Enter bills as they arrive, keep vendor names and invoice numbers consistent, and track payments with references. An accounts payable spreadsheet with a duplicate check handles most of that. If data entry is the bottleneck, automating invoice entry and exporting to CSV or Google Sheets keeps both sides of the match in the same format. Plans for higher volumes are on the pricing page.