Free Supplier Statement Reconciliation Template (Excel Workbook)

What is in the template and what does it do?

The bridge is just the list of differences between the supplier's closing balance and yours. Search for a vendor statement reconciliation template or a creditors reconciliation sheet and you are after the same thing. In this file you only type into three places. Balances and sign-off go on the Reconciliation sheet, and the lines you paste go on the Statement and Ledger sheets. The rest fills itself in from formulas.

The sheet calls a line Matched when the document and the amount both agree. If they do not, the line needs a cause code, and the Checks sheet fails any difference that has none. Spelling is ignored when it compares documents, so PF/31842, PF31842 and 31842 all count as the same one. A credit note numbered 100 will not pair with an invoice numbered 100. Payments pair on amount alone, since the supplier and your ledger rarely put the same reference on a payment.

The other sheets are support. Read me explains matching, signs and limits. Lines lists every exception in one place. Cause codes holds 31 codes, each with an owner, a next move and wording for the supplier. Checks runs 12 pass or fail tests and a single SIGNABLE cell. Example is a finished reconciliation on made-up data, and Chase list collects the lines you still need to raise with the supplier.

The Pemberton September statement, fully reconciled

Here is the finished case first. Ridgeway Cabinet Works buys fixings from Pemberton on account RCW-8814, in Australian dollars, GST included in every amount. The statement is a balance-forward one dated 30 Sep 2026. It opens on 5,274.30, lists the month’s activity and closes on 11,078.45. The purchase ledger, run as at the same date, closes on 7,082.30.

All the names, references and amounts here are made up, as they are on the workbook’s Example sheet. The GST treatment is general information, not tax advice.

Most of the gap, 2,831.35 or about 71 percent, is timing, and nobody made a mistake there. Ridgeway’s payment had not reached Pemberton’s books when the statement ran, and an invoice dated on the last day had not arrived. The postings are routine. The only real conversation with the supplier is about a unit price.

After the four postings the ledger reads 8,136.10 and the sheet’s Still different line shows 2,942.35. Pemberton then confirms the 1,672.30 on PF/31871, so you post the 45.00 correction. The ledger goes to 8,181.10 and Still different drops to 2,897.35. Two things remain open: the 2,831.35 of timing and the 66.00 price question.

How do you reconcile a supplier statement in Excel? Get both sides ready

Run your ledger as at the statement date, 30 Sep here, and hold your side to it. If you run it on the 3rd, it picks up payments and invoices that the statement could not have known about. You then spend time chasing differences that were created by the later run date.

Read the statement before you copy it

Before you paste anything, add the statement lines to the opening balance. You should land on 11,078.45. If you do not, you have misread a line, or the supplier's statement does not add up in the first place. Sort that out now. If you leave it, the Checks sheet will flag it later and it will be more annoying then. Some suppliers send an open-item statement. It lists only what is still unpaid and has no payments on it. With those, put 0.00 in both opening balances and run an open-items report from your ledger as at the same date.

Pull your side of the ledger

You need every bill, credit note and payment for this supplier in the statement period, plus the opening and closing balances. Which report you need depends on the software. The guide for Xero and the guide for QuickBooks Online go step by step, and the cards below give the short version.

For Vista, report setups differ between installations, so we give no click path here. The Vista page explains the daily feed instead.

Paste, match and read the status

1. 1

   Fill the Reconciliation header

   Supplier name, account RCW-8814, statement date 30 Sep 2026, AUD on both currency cells. Opening balances: 5,274.30 on each side. Closing balances: 11,078.45 per the statement and 7,082.30 per the ledger.

2. 2

   Paste the statement

   Ten lines into Statement columns A to D, leaving out the opening line because you entered it in step 1. Invoices are positive. Credit notes and payments are negative. Pick Invoice, Credit or Payment in column C.

3. 3

   Paste the ledger

   Nine lines into Ledger columns A to D, on the same sign rule. If one side stores credits as positives, flip them while you paste. Mixed signs often stop a first reconciliation from balancing, because every credit then shows as a difference of twice its value.

4. 4

   Read the Status column

   The sheet matches on reference first, then amount, and writes a status against every line. Anything that is not Matched needs a decision from you.

Before any cause code is typed, the Reconciliation sheet shows an unexplained residual of 3,996.15 and the words NOT SIGNABLE. Two checks are failing at that point, because the bridge does not foot and eight differences have no code. Do not clear it by editing balances. Code the lines. We recalculated this exact case in LibreOffice to confirm these results.

The Pemberton case has a trap. The duplicate PF/31914A does not show up as Check duplicates, because its reference differs from PF/31914. It appears as Ledger only, with a suggested cause of INV-NOT-ON-STMT. Nothing in a formula connects the A to the original. A person sees that the amount is identical to PF/31914 and codes it DUP-BOOKED instead. The suggested cause is a hint. It never fills in your cause code.

Why do supplier and ledger balances differ?

Eight differences, eight different causes. Real accounts often repeat themselves, with the same unposted credit notes or the same tax slip month after month, but this statement has no repeats. The Cause codes sheet lists 31 and the Read me suggests starting with eight. These are the eight this statement produced.

Two causes did not turn up here but will in your own work: a sign flip and a bill posted to the wrong supplier. The guides on missing credit notes and duplicate payments and on common reconciliation problems cover more. To see how each one looks on a statement, use the exception types in the cheat sheet. Every cause has a code, an owner and ready wording on the Cause codes sheet.

How do you know the reconciliation is right?

Type the eight codes and the sheet recalculates. The unexplained residual drops to 0.00, but one check still fails: the two lines marked for the supplier have not been raised. Once the query below has been sent, put Yes in the Query raised column on PF/31871 and PF/31952. All twelve checks then read PASS, and the sign-off cell changes to SIGNABLE.

The residual is the statement balance minus the ledger balance minus every difference you have coded. If it is not zero, a line is missing from your paste, a balance is mistyped, or a sign is wrong.

A zero residual means every dollar of the gap has a cause. It says nothing about how many causes there are. In the Pemberton case the net gap is 3,996.15 but the exceptions total 6,296.55 when you ignore their signs. 5,146.35 pushes the statement above the ledger and 1,150.20 pushes it below, which is the 388.80 credit note plus the 761.40 duplicate. Make those two a little bigger and the net could shrink toward nothing while eight problems stayed in the books. So look at the count of exceptions as well as the total. The Reconciliation sheet shows it.

Email the supplier, then sign off

Post your own corrections first, and leave timing alone. Then send the supplier only the lines that need their answer, and mark those two lines Yes in the Query raised column. The Chase list sheet collects them and offers wording, and the note below is that wording filled in for the Pemberton case.

Once the query is sent and marked, sign it off. The Reconciliation sheet has a Prepared by line and a Reviewed by line, and the reviewer should be someone other than the preparer. It prints on two pages. The first is the bridge that proves the balances. The second has the actions, the ageing of unresolved items and the signatures. Keep the print with the statement PDF. How often to do this, and which suppliers need it before each payment run, is in the month-end checklist.

How the formulas work

You can use the workbook without reading a formula. This section is for the day you adapt it. The table shows the pattern behind three of them, simplified: the real column letters differ.

The sheet compares totals per document number, not row against row. A row-by-row tick-off would match both copies of a bill booked twice under the same reference. Compared by total, 1,522.80 against a statement line of 761.40 shows up straight away, and the status reads Check duplicates.

To build the match key the sheet takes out spaces and punctuation, drops any letters before the first digit, and removes leading zeros. It goes wrong in two cases. If someone types a capital O instead of a zero, 4O17 and 4017 look like different documents. And a reference with a suffix, like 4541-1, turns into 45411, which can collide with a different bill. For either one, type any word in the Match key override column on both rows and the sheet will pair them.

We would have liked XLOOKUP, because it makes the pull a shorter formula and it works in Google Sheets. But Microsoft's XLOOKUP page lists it as unavailable in Excel 2016 and 2019. A colleague on an older copy would open the file and get errors. So the sheet uses SUMIFS and COUNTIFS, which are longer but work everywhere.

Does it work in Google Sheets and older Excel?

The workbook sticks to common functions: SUMIFS, COUNTIFS, INDEX, MATCH, IF and their usual neighbours. There are no macros, no tables, no sheet protection and no add-ins. In Excel, open the file and start at the Reconciliation sheet.

Steps to open it in Google Sheets: upload the .xlsx to Google Drive, double-click it, then choose File and Save as Google Sheets. Or, in an open Sheets file, choose File, Import and pick Create new spreadsheet (as of 30 Oct 2026, per Google Docs Editors Help).

Printing needs one habit. The data sheets print all 300 rows, so select the filled rows and use Print selection. The Reconciliation sheet needs nothing and prints on two pages.

When is a spreadsheet the wrong tool?

One copy of the file holds 300 lines a side and 150 exceptions, for one supplier in one currency. Most people get annoyed at the retyping well before they hit those limits. If you reconcile a few suppliers a month and can copy the statement lines straight out of the PDF, the sheet does the job. If you have dozens of suppliers, each with a different PDF layout, you will spend the month retyping.

Where Statement Zen fits

We make Statement Zen, which does the typing for you. A PDF like the Pemberton one, with its 11 lines and 11,078.45 closing balance, can be forwarded by email. It is then matched against your ledger in Xero, QuickBooks Online or Vista, and the differences wait in your dashboard. The details are on the page to reconcile Xero supplier statements automatically and the QuickBooks Online reconciliation page. Send one statement through the free reconciliation, and check what it costs on the pricing page.

Questions people ask

Sources

• Microsoft Support: XLOOKUP function, read 30 Oct 2026
• Google Docs Editors Help: XLOOKUP, read 30 Oct 2026
• Xero Central: Select contents and display order in a new report, read 30 Oct 2026
• Google Docs Editors Help: Use both Excel & Sheets, read 30 Oct 2026
• Australian Taxation Office: How GST works (rate of 10 percent), read 30 Oct 2026
• Xero Central: Payable Invoice Detail report (US version of the article), read 30 Oct 2026
• Intuit: Print a customer or vendor register, read 30 Oct 2026
• Intuit: Export reports to Excel in QuickBooks Online, read 30 Oct 2026

What changed

• 30 Oct 2026: First published, alongside version 2 of the workbook (nine sheets, 31 cause codes, 12 checks).
• 31 Oct 2026: Ran Payable Invoice Detail in Xero’s Demo Company (AU) and Vendor Balance Detail in Intuit’s Test Drive company, and replaced the hedged notes on both cards with what each report showed.