A supplier emails a statement showing that you owe 15,530.50. Your books say 9,125.50. Nobody has done anything wrong yet, but you cannot pay either number until you know why they are 6,405.00 apart. Maybe a bill never reached your inbox. Maybe a payment is still in the post. Maybe a credit note was issued and never recorded. The only way to find out is to compare the two accounts line by line, and that is what a supplier statement reconciliation does.
Done at month end, it catches missing invoices before the supplier chases you, unrecorded credits before you overpay, and payments that crossed in the post before anyone assumes you are late. Done badly, it takes a whole afternoon of ticking lines on a printout. This guide shows how to reconcile a supplier statement to your accounts payable in Excel, using reports exported from QuickBooks Desktop, with the formulas that make the matching automatic and a full worked example.
What a supplier statement reconciliation is
A supplier statement is the supplier's record of your account: invoices they have charged, payments they have received and credits they have issued, with a closing balance. Your accounts payable ledger records the same account from your side. In theory the two balances agree. In practice they rarely do, for ordinary reasons:
- You have not entered a bill the supplier sent, or it never arrived.
- The supplier has not yet received a payment you have recorded.
- A credit note exists on one side only.
- An invoice was entered with a different amount, often from a typing slip.
- The two sides use different dates, so an item falls in different months.
- The supplier applied a payment to a different invoice from the one you intended.
A reconciliation does not force the balances to agree. It lists every difference, explains it and shows what to do next, so the adjusted balances match and the unexplained difference is zero.
Step 1: get the data out of QuickBooks Desktop
You need your side of the account in a spreadsheet. QuickBooks Desktop has three reports that work well, all under Reports, then Vendors & Payables:
- Vendor Balance Detail: every transaction for each vendor with a running balance. Best for a full reconciliation.
- Transaction List by Vendor: every bill, payment and credit in a date range.
- Unpaid Bills Detail: only open bills, useful when the supplier statement lists only open items.
Set the date range to cover the statement period, plus the earlier invoices that make up the opening balance. Then use the Excel button at the top of the report and choose Create New Worksheet. Keep the export as it comes: do not delete the vendor headings, the blank spacer columns or the total rows. Cleaning them by hand is exactly the time sink you are trying to avoid, and a template can deal with them.
Check one thing before you move on: signs. Make sure bills are positive and payments and credits are negative in your data, or the other way round, but consistently, and match the same convention on the supplier's statement.
Step 2: enter the statement
On a clean sheet, record the statement header and its lines.
Header: supplier name, statement date, the start of the period, the opening balance and the closing balance the supplier shows.
Lines: date, reference, description and amount, with charges positive and payments and credits negative.
Then add a check that the statement agrees with itself:
=OpeningBalance + SUM(StatementAmounts) - ClosingBalance
This must be zero. If it is not, you have mistyped a line or the statement has an item you have not copied, and no reconciliation will work until you fix it. It is the most common reason for an "unexplained difference" at the end.
Step 3: handle the period correctly
Two cut-off rules keep the comparison fair.
- Opening balance. QuickBooks lines dated before the period start make up your opening balance:
=SUMIFS(QBAmount, QBDate, "<"&PeriodStart). If it differs from the supplier's opening balance, that is a reconciling item in its own right and usually means an old item is still open on one side. - Statement date. QuickBooks lines dated after the statement date belong to the next period, so leave them out of the comparison. The supplier cannot have shown them yet.
Step 4: match the lines
Matching is the heart of the job. Do it in two passes.
Pass 1: match on the reference
The same invoice rarely looks the same on both sides. You might have INV-24518 where the supplier prints 24518 or Inv 024518. Clean both references before comparing them:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2), " ", ""), "-", ""), "INV", "")
Then remove leading zeros by turning a numeric reference into a number and back into text:
=IFERROR(VALUE(B2) & "", B2)
Now the two references are comparable, and MATCH finds each statement line in QuickBooks:
=MATCH(CleanRef, QBCleanRefs, 0)
If it finds the reference, compare the amounts with INDEX: equal means Matched, different means Amount differs.
Pass 2: match what is left on the amount
Payments are the lines that fail on reference. Your record says "Check 1043" and the supplier's says "Receipt R-8812". Neither reference exists on the other side. For lines left over after pass 1, look for the same amount within a date window, say 5 days:
=COUNTIFS(QBAmount, Amount, QBDate, ">="&Date-Window, QBDate, "<="&Date+Window, QBMatched, "")
The last condition skips QuickBooks lines already used, so one payment cannot be matched twice. A hit marks the line Matched on amount, which is a lower level of certainty than a reference match and worth a glance.
The five outcomes
Every line on both sides ends in one of five states:
| Status | Meaning | Usual next step |
|---|---|---|
| Matched | Same reference and amount | None |
| Matched on amount | Same amount and date window, different reference | Confirm it is the right item |
| Amount differs | Same reference, different amount | Check the invoice; correct whichever side is wrong |
| Not in QuickBooks | On the statement only | Request a copy of the bill, then enter it, or dispute it |
| Not on statement | In QuickBooks only | Usually a payment in transit; send remittance details |
Step 5: a worked example
A fictional packaging supplier sends a statement dated 31 October. Both sides agree that the account opened on 1 October at 6,200.00.
What the supplier shows:
| Date | Reference | Amount |
|---|---|---|
| 3 Oct | INV 24518 | 4,850.00 |
| 9 Oct | INV 24533 | 2,130.50 |
| 16 Oct | Receipt R-8812 | -6,200.00 |
| 17 Oct | INV 24561 | 5,975.00 |
| 20 Oct | CN-3307 | -640.00 |
| 22 Oct | INV 24577 | 1,960.00 |
| 26 Oct | CN-3319 | -225.00 |
| 28 Oct | INV 24602 | 1,480.00 |
The closing balance is 15,530.50.
What QuickBooks shows:
| Date | Reference | Amount |
|---|---|---|
| 3 Oct | INV-24518 | 4,850.00 |
| 9 Oct | INV-24533 | 2,310.50 |
| 15 Oct | Check 1043 | -6,200.00 |
| 17 Oct | INV-24561 | 5,975.00 |
| 20 Oct | CN-3307 | -640.00 |
| 28 Oct | INV-24602 | 1,480.00 |
| 29 Oct | Check 1051 | -4,850.00 |
The balance in QuickBooks is 9,125.50, which is 6,405.00 lower.
Running the two passes gives this result:
- Matched on reference: INV 24518, INV 24561, CN-3307 and INV 24602, four lines. The cleaning step is what lets
INV-24518findINV 24518. - Matched on amount: the payment of 6,200.00. The supplier received it on 16 October as receipt R-8812, one day after you recorded check 1043, so it matches on amount and date.
- Amount differs: INV 24533. QuickBooks has 2,310.50, the supplier has 2,130.50. Two digits are transposed, and the difference is 180.00.
- Not in QuickBooks: INV 24577 for 1,960.00, a bill that was never entered, and CN-3319 for 225.00, a credit note you did not know about.
- Not on statement: check 1051 for 4,850.00, written on 29 October, two days before the statement date. The supplier has not received it.
The reconciliation statement
Start from the supplier's balance and walk it to yours:
| Amount | |
|---|---|
| Balance per supplier statement | 15,530.50 |
| Less: bill on the statement not in QuickBooks (INV 24577) | -1,960.00 |
| Add: credit note on the statement not in QuickBooks (CN-3319) | 225.00 |
| Less: payment in QuickBooks not yet on the statement (check 1051) | -4,850.00 |
| Add: amount difference on INV 24533 (QuickBooks is higher) | 180.00 |
| Adjusted balance | 9,125.50 |
| Balance per QuickBooks | 9,125.50 |
| Unexplained difference | 0.00 |
Every unit of the 6,405.00 gap is now accounted for, and each item has an action:
- Ask the supplier for a copy of INV 24577, check it is genuine and enter it. This raises your balance by 1,960.00.
- Ask for credit note CN-3319 and record it. This lowers your balance by 225.00.
- Send the supplier remittance advice for check 1051, so they can apply the payment.
- Pull INV 24533 and see which amount is right. If the supplier is right, correct the bill in QuickBooks, which lowers your balance by 180.00.
After those entries, your books will show 10,680.50, and the supplier's statement will show the same figure once they apply check 1051. Without the reconciliation, someone might have paid the statement's 15,530.50 in full and paid the 4,850.00 from check 1051 twice.
Common reconciling items and what to do
| Item | Why it happens | What to do |
|---|---|---|
| Invoice on statement, not in books | Bill lost, mis-sent or not yet entered | Get a copy, approve it, enter it |
| Invoice in books, not on statement | Supplier has not billed it, or dated it later | Check the supplier's invoice date |
| Payment in transit | Posted or mailed but not yet received | Send remittance advice |
| Credit note on one side only | Return or discount not communicated | Request or record the credit note |
| Amount difference | Typing slip or a price change | Compare against the invoice and purchase order |
| Duplicate invoice | Entered twice, or billed twice | Void or dispute the duplicate |
| Different opening balances | An old item is still open on one side | Reconcile the earlier period first |
| Payment applied to wrong invoice | Supplier chose a different invoice | Ask the supplier to reallocate |
Mistakes that stop a reconciliation from working
- Matching on amounts alone. Two invoices for 1,480.00 will both match the same line. Match on the reference first.
- Ignoring the cut-off. An item dated after the statement date is not a difference, it is timing.
- Mixing up signs. If credits are positive on one side and negative on the other, nothing will match.
- Not checking the statement adds up. A mistyped line causes an unexplained difference that is not real.
- Reconciling only when the supplier complains. At month end, differences are small and fresh. After six months they are a project.
- Not keeping the sign-off. A reconciliation without a preparer, reviewer and date is difficult to defend to an auditor.
A reconciliation template that does the matching
The two passes, the five statuses and the reconciliation statement are the same for every supplier, and so is the effort of rebuilding them each month. The Supplier Statement Reconciliation Template for QuickBooks Desktop does all of it from two pastes.
Export Vendor Balance Detail, Transaction List by Vendor or Unpaid Bills Detail from QuickBooks Desktop and paste it in as it is. The header row and the Type, Date, Num, Memo and Amount (or Debit and Credit) columns are found automatically, spacer columns, vendor headings and total rows are handled, and you can choose any column by hand if your report uses different headings. Pick the supplier from the list of vendors in the report, enter the statement date, period start and balances, then type or paste the statement lines.
Every line is matched on the cleaned reference first, then on the amount within a date window you set, and marked Matched, Matched on amount, Amount differs, Not on statement or Not in QuickBooks with a suggested next step. The Reconciliation tab sets out the statement above with the unexplained difference, warns if the statement lines do not add up to the closing balance, lists the items to fix, charts the reconciling items and ends with a sign-off block that prints or saves as PDF. It holds 3,000 report rows and 500 lines on each side, locks its formulas without a password and contains no macros.
QuickBooks is a trademark of Intuit Inc. The template is independent: it is not made by, affiliated with or endorsed by Intuit, and it works with reports you export yourself.
If you need a simpler tool or a related check, these templates may help:
Common questions
What is a supplier statement reconciliation?
A check that the balance a supplier says you owe agrees with your accounts payable, with every difference explained, such as invoices you have not entered or payments the supplier has not received yet.
How do I reconcile a supplier statement in Excel?
Export your vendor transactions from your accounting software, enter the statement lines, clean the references so they compare, match on the reference and then on amount within a date window, and list every unmatched line. Then walk the supplier's balance to yours with the reconciling items, and check the unexplained difference is zero.
Which QuickBooks Desktop reports should I use?
Vendor Balance Detail for a full reconciliation, Transaction List by Vendor for a date range, and Unpaid Bills Detail when the statement shows only open items. Export any of them to Excel with Create New Worksheet.
How often should I reconcile supplier statements?
At least monthly for your main suppliers, and before each payment run for any supplier whose statement you doubt. Small, recent differences are easy to resolve.
What causes an unexplained difference?
Usually a statement line that was mistyped, opening balances that differ, a missing line or a sign error. Check that the statement lines add up to the closing balance first.
Do I need to reconcile every supplier?
Not necessarily. Start with suppliers with the largest balances or the most transactions, and those that send statements regularly.
Does it work in Google Sheets?
The template is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over and charts may look different, so Excel is the better fit.
Reconciling supplier statements each month turns a surprise at payment time into a short list of actions. You can preview the Supplier Statement Reconciliation Template for QuickBooks Desktop free; downloading it is included in the OpenWorksheet monthly plan, and you can also buy this template on its own. The supplier and figures in this guide are a fictional example, and this is general information rather than accounting advice.