Excel Reconciliation: The Complete Guide to Reconciling Two Data Sets

Most tutorials stop at =IF(ISNA(VLOOKUP(...)),"Missing","Found") and call it reconciliation. That formula will find you a large number of “missing” rows in a perfectly reconcilable file, because the two sides almost never use identical text. This guide is about the parts that actually decide whether your reconciliation is correct.

Reconciliation is not the same as comparison

The two get used interchangeably, and the confusion causes real problems when you are deciding which technique to reach for.

If you only need to know what changed between two versions of a sheet, you want a file comparison instead. Everything below assumes you are reconciling two things that carry amounts.

The two files you always start with

Every reconciliation has a book side and an external side. The book side is what your accounting system says. The external side is what somebody else says.

Once you see that, the rest of this guide applies to all five. The specific procedures are covered in the bank, vendor, GST, TDS and invoice guides.

The five steps of a reconciliation that holds up

Step 2 and 3 in practice: building a key that actually matches

Here is a realistic vendor reconciliation. Your vendor, Shree Balaji Enterprises, sends a statement. Your ledger for the same period looks like this:

And their statement says:

Build a normalised key column on both sides. In the ledger, add a helper column and extract only the last four digits of the invoice number:

For amounts, force both sides to a number before comparing: =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"₹",""),",","")). Text that looks like ₹1,18,000 will never equal the number 118000 until you do this.

Step 4: choosing the matching method

There is no single best function. The right choice depends on whether you are looking for existence, for a first match, or for a total.

A practical sequence for a single-key reconciliation is: COUNTIF first to split rows into present and absent, XLOOKUP next to pull the amount across for the rows that are present, and SUMIFS for any group totals you need at the end.

Step 5: amounts that do not tie exactly

Two files can agree completely and still differ by paise. Foreign vendor invoices carry rounding, TDS deducted at source changes what was actually paid, and credit notes get netted off. If you compare amounts with a plain equality test you will produce a working paper that is technically correct and practically unreadable.

Compare the difference, not the values:

The case that breaks every lookup: one-to-many

This is the single most common reason a “finished” reconciliation turns out to be wrong, and it is almost never taught properly.

Your customer pays ₹2,36,000 by RTGS on 18 September. Against that single receipt, your books clear four invoices:

A lookup on the receipt amount finds nothing, because ₹2,36,000 never appears in the invoice column. A lookup on each invoice finds nothing, because none of those amounts appears in the payment column. The rows are individually unmatched and collectively a perfect match.

The workable Excel approach is to match upward, on the total:

1. 1Classify each entry as a debit or a credit before you do anything else. Mixing the two is what makes a reconciliation impossible to interpret later.
2. 2Tag each invoice with the receipt that cleared it — usually the voucher number or UTR from your ledger. If your ledger already records this, you have a ready-made group key.
3. 3Use SUMIFS on that group key to total the invoices per receipt, and compare that total against the receipt amount with your tolerance.
4. 4Anything that does not balance at group level drops down to invoice-level matching, and whatever survives both passes is your genuine exception list.

What the output should look like

A reconciliation is not a column of “Found” and “Not Found”. It is a document. Four separate result sets, always:

Finish with a three-line summary at the top: total per book side, total per external side, and the sum of the explained differences. If those do not reconcile, nothing below them is trustworthy.

What breaks in Excel reconciliation

Worth knowing before you commit an afternoon to building one:

• Conditional formatting cannot reference another workbook. A rule that highlights differences works only when both ranges live in the same file, so a two-workbook reconciliation cannot be done with formatting alone.
• Lookups return the first match and stop. Duplicate invoice numbers on one side will quietly produce a plausible but wrong result.
• Slowdowns arrive faster than you expect. A COUNTIF against a 100,000-row range, repeated down 100,000 rows, is a very large number of comparisons. Files that took seconds at 5,000 rows can take minutes at 50,000.
• Row insertion breaks highlighting. Insert a row into one of two formatted ranges and every row below it registers as a difference.
• Nothing is recorded about why a row matched. Six months later, when an auditor asks, the answer has to come from whoever built the file.

None of these are reasons to avoid Excel. They are reasons to keep the file small, the key clean, the tolerance documented, and the working paper a separate, saved output rather than a live formula sheet.

When it is worth moving off Excel

Excel is the right tool for a one-off reconciliation, for a handful of files, and for anything you need to show your working on. It becomes the wrong tool when the same reconciliation has to run every month across many files, when the matching needs tolerance, fuzzy and group logic at the same time, and when the result needs to be a repeatable document rather than a formula sheet somebody has to re-check.

That is the point at which a dedicated reconciliation tool earns its place. It is not that Excel cannot do it — it is that the maintenance cost of keeping a large formula workbook correct, month after month, usually exceeds the cost of the tool.