Excel Reconciliation12 min read

Vendor Reconciliation in Excel: Format, Process and Worked Example

You owe the vendor one figure and they say you owe another. Vendor reconciliation is the working paper that closes the gap — and the five-column format below is the one reviewers expect to see.

Short answer

Vendor reconciliation is the process of matching your accounts-payable ledger for a vendor against the statement of account that vendor sends you, so that both sides agree on what is owed. In Excel it is done with a five-column format — Reference, Amount as per books, Amount as per vendor statement, Difference and Remarks — with one row per invoice or credit note and a total at the bottom that must come to zero.

Why it is worth the time

A vendor reconciliation is usually framed as a control exercise, and it is. But the reasons it actually pays for itself are more concrete:

Overpayment. Pay an invoice twice because it arrived in two formats, and the recovery is slow and sometimes never happens.
Duplicate invoices in your books. The same supply invoice entered twice looks exactly like a legitimate second invoice until you reconcile.
Credit notes never accounted for. A credit note sitting in the vendor's records but not in yours becomes a payment you make that you did not owe.
Input tax credit. For a GST-registered vendor, an invoice in your books that is not in their GSTR-1 becomes an input tax credit mismatch later. Catching it here is cheaper.
Supplier confidence. Vendors prioritise the customers whose accounts they can trust. A monthly reconciliation removes the disputes before they become holds.

The five-column format

Column	What goes in it
Reference	Invoice or credit note number with its date — the key you will match on
Amount as per books	What your accounts payable ledger shows for that document
Amount as per statement	What the vendor's statement shows for the same document
Difference	Books minus statement, so you can see the direction of the gap at a glance
Remarks	Why the row differs — in transit, unallocated payment, credit note, rate difference

One row per document. No grouping by vendor, no merged cells.

A worked example

Krishna Packaging Pvt Ltd (GSTIN 07AABCK2231L1ZP) sends you a statement for August 2025 showing a closing balance of ₹3,42,150. Your ledger for the same period shows ₹3,98,300. A gap of ₹56,150.

Reference	As per books (₹)	As per statement (₹)	Difference (₹)	Remarks
KP/2025/1874 — 04-08-2025	1,18,000	1,18,000	0	Matched
KP/2025/1902 — 12-08-2025	82,050	82,050	0	Matched
KP/2025/1955 — 21-08-2025	1,45,000	1,45,000	0	Matched
CN/2025/0212 — 24-08-2025	(23,600)	0	(23,600)	Credit note not recorded by vendor
KP/2025/1988 — 29-08-2025	76,850	0	76,850	Invoice received 02-09-2025, in transit
Payment 14-08-2025	(2,86,600)	(2,83,000)	(3,600)	TDS withheld not allocated by vendor
Total	3,98,300	3,42,150	56,150	—

The ₹56,150 gap is now four explained lines rather than one mystery: a credit note the vendor has not recorded, an invoice of yours still in transit, and ₹3,600 of tax deducted at source that the vendor has not yet allocated against the invoice. The total of the Difference column still stands at ₹56,150, which is exactly the gap you started with — that is the proof the working paper is complete.

The difference column must total to the gap you started with

Until every row is explained, the Difference column total will not equal the balance difference between the two sides. When it does, you have accounted for every rupee. That single check is what separates a reconciliation from a list of matches.

Step by step

Get the statement and extract your ledger for the same dates

Ask the vendor for a statement covering the exact period you are reconciling. A statement to a different cut-off will produce differences that are purely timing.

Normalise the document number on both sides

Vendors write their own invoice numbers differently when they re-key them — KP/2025/1874, KP-1874, 1874. Build a normalised key on both sides before you match, exactly as described in the reconciliation guide.

Match document by document

Match on the normalised number first, then on the amount. Anything that matches on the number but not on the amount is an amount difference, not a missing item, and needs its own row.

Categorise the differences

In transit, unallocated payment, credit note, rate or quantity difference, or duplicate. Every open row gets one of those labels.

Total and confirm

Total both amount columns and the difference column. Then send the open items to the vendor for confirmation rather than adjusting your books on your own understanding of the cause.

The discrepancies you will actually find

Discrepancy	Which side it appears on	How to treat it
Invoice in transit	Your books only	Legitimate timing — confirm the vendor records it next period
Payment not allocated	Your books only	Vendor holds it as an advance or against another invoice; get the allocation corrected
Credit note unrecorded	One side only	Send the credit note reference and get it booked
Rate or quantity difference	Both sides, different amounts	Root cause is the purchase order or the delivery — resolve before adjusting
Duplicate invoice	Either side	Reverse in the books; investigate why it was entered twice
TDS not allocated	Your books only	Common — the vendor has the cash but has posted it elsewhere

Partial payments and one payment clearing several invoices

This is where a simple lookup reconciliation falls apart. Your ₹2,86,600 remittance on 14 August cleared four invoices and carried a TDS deduction. Nothing about that payment matches any single invoice amount.

Invoice	Amount (₹)	Cleared by the 14-08 remittance
KP/2025/1801	68,400	Yes
KP/2025/1823	51,200	Yes
KP/2025/1840	94,000	Yes
KP/2025/1861	73,000	Yes
Total	2,86,600	—

Match at the payment level: tag each invoice with the payment voucher that cleared it, use SUMIFS to total the invoices per voucher, and compare that total to the remittance within your tolerance. A partial payment appears as a genuine open balance rather than as a mismatch — the two cases look identical in a lookup but are completely different to an accountant.

The same logic is covered in more depth in invoice matching in Excel.

What breaks in Excel

Every vendor writes their statement differently. One sends a PDF, one sends an Excel with three header rows, one sends a screenshot. Standardising the layout is often more work than the reconciliation itself.
Large vendors have hundreds of small invoices. The file grows, and so does the time each lookup takes.
One payment to many invoices defeats a lookup. Without a group key you will report correct payments as mismatches.
Nothing records why a row was treated as matched. The working paper shows the conclusion, not the reasoning, unless you write it in the Remarks column yourself.

When it is worth automating

If you reconcile a handful of vendors occasionally, Excel is the right answer and this format is all you need. The case for a dedicated tool appears when reconciliation runs monthly across many vendors, when vendor statement layouts vary enough that you rebuild the sheet each time, and when the matching genuinely needs tolerance and group logic rather than a lookup.

That is what a reconciliation tool does — applies the same rules you would apply by hand, but consistently and without the formula maintenance. The Piloteq Automate reconciliation page shows what the output looks like.

If you would rather not build this by hand

Match the vendor ledger against their statement

Piloteq Automate reconciles your vendor ledger against the vendor's statement directly, applying exact, tolerance, fuzzy and last-digit rules in priority order. Group matching handles the case where one payment clears several invoices, and every record comes back with a status and a written reason.

✓1:1, 1:N, N:1 and N:N group matching
✓Fuzzy matching for party names spelled differently
✓Duplicate detection for repeated invoice numbers
✓Amount difference analysis with the delta per record

₹1,499 · Single PC License · 12-month license

See how Piloteq Automate handles reconciliation →

Frequently asked questions

What is the format of vendor reconciliation in Excel?+

Use a five-column layout: Reference (invoice or credit note number and date), Amount as per your books, Amount as per the vendor's statement, Difference, and Remarks. One row per document. At the bottom, total each of the three amount columns. The total of the Difference column must be zero once every row has been explained — if it is not, you have not finished.

Why does my vendor's statement not match my books?+

In practice, five things account for nearly all of it: invoices in transit that the vendor has recorded but you have not yet received, payments you have made that the vendor has not yet allocated, credit notes one side has recorded and the other has not, a payment allocated to the wrong invoice on the vendor's side, and a genuine rate or quantity difference. Grouping the differences into those five categories is faster than chasing them one by one.

How do I reconcile when I have paid one amount against several invoices?+

Match at the payment level, not the invoice level. Tag each invoice with the payment voucher that cleared it, use SUMIFS to total the invoices per payment, and compare that total to the payment amount within a small tolerance. Only the groups that fail to balance need invoice-by-invoice investigation.

What are the common vendor reconciliation interview questions?+

Interviewers tend to ask: what is vendor reconciliation and why is it done; what is the difference between vendor reconciliation and general ledger reconciliation; how you would handle a vendor statement that shows a balance you cannot trace; how you treat a credit note that appears in only one set of books; what you would do about an invoice that is duplicated in your accounting system; and how you would reconcile a vendor with hundreds of small invoices. They are checking your process, so answer in categories rather than describing a formula.

How often should vendor reconciliation be done?+

Monthly for your significant vendors, and at least quarterly for the long tail. Reconciling monthly keeps the number of open items small enough to resolve. Reconciling once a year, just before closing the books, is how differences that are two years old end up written off because nobody can trace them any more.

Should I send my vendor a copy of the reconciliation?+

For any significant difference, yes. The reconciliation is the document you send with a request for confirmation, and having the vendor formally agree the balance is what converts a spreadsheet into evidence. Keep the signed or emailed confirmation with the working paper.