Free vendor statement reconciliation template for Excel
A free Excel template for reconciling a vendor statement with your QuickBooks Online A/P Aging Detail - plain COUNTIF and SUMIF formulas, a summary that proves the difference, and a worked example.
Download the template (.xlsx) - free, no signup.
It reconciles one vendor statement against the A/P Aging Detail report from QuickBooks Online using plain formulas you can read and change. It comes filled in with a made-up example vendor, Northwind Brake Supply, so you can see the results before you paste your own.
What's inside
| Sheet | What it does |
|---|---|
| How to use | The steps below, and what the template can't do |
| Summary | Walks the statement balance to your books balance. "Unexplained" should be 0.00 |
| Vendor statement | Paste the vendor's lines here: invoice number, date, PO, amount |
| Your books | Paste this vendor's lines from the A/P Aging Detail. Shows which of your bills the vendor doesn't have |
| Reconciliation | For every statement line: is it in your books, what's the difference, and what it means |
How to use it with QuickBooks Online
- Delete the example rows in Vendor statement and Your books.
- Paste the vendor's statement: invoice number, date, PO and amount. Credits and payments go in as negative amounts.
- In QuickBooks Online, run Reports → A/P Aging Detail as of the statement date and Export to Excel. Copy this vendor's lines into Your books: the Num column as the invoice number, the date, and the Open balance.
- Read Reconciliation (statement side) and the last column of Your books (your side). Green rows agree, amber rows differ in amount, red rows are on one side only.
- Check Summary. When the unexplained line says 0.00, every cent of the difference is accounted for.
The formulas, if you'd rather build your own
| Column | Formula | Why |
|---|---|---|
| In your books? | =IF(COUNTIF('Your books'!$A$2:$A$1000,A2)>0,"Yes","No") |
Is the invoice number anywhere in your books? |
| Amount in your books | =SUMIF('Your books'!$A$2:$A$1000,A2,'Your books'!$C$2:$C$1000) |
Adds every line with that number, so a duplicate shows up as a difference |
| Difference | =B2-D2 |
Statement minus books |
| On the statement? (in Your books) | =IF(COUNTIF('Vendor statement'!$A$2:$A$1000,A2)>0,"Yes","No") |
The same check in the other direction |
The summary adds up three groups - statement lines not in your books, lines in your books not on the statement, and amount differences - and compares them with the total difference.
Where a template stops working
Open the example and look at what it reports:
INV-51200490on the statement and51200490in your books are the same invoice, but exact matching reports it twice: missing from your books, and not on the statement.51200555and51200565are one invoice with a typo - same amount, one digit off.PMT-0915on the statement and checkCHK-2207in your books are the same payment of 1,500.00 under two numbers.51200433was entered twice in the books, so it shows up as an amount difference of (455.25).
The summary still says 0.00 unexplained - the money adds up, but the groups are wrong. That's why "it balances" isn't the same as "it's reconciled". A template also can't tell you whether a "missing" invoice was already paid (that needs the Transaction List by Vendor), and it gets messy when one statement covers several of your companies. More on the usual causes in Why your A/P aging doesn't match the vendor statement.
Template or tool?
The template is fine for a few vendors with short statements and clean invoice numbers. If your statements are long, the numbering is messy, payments come under other numbers, or you reconcile several QuickBooks Online companies or every week, Wefinly does the matching for you - all of the cases above - and gives you an Excel workpaper with a live dashboard. It's free while in beta, and your files never leave your browser.