What Is Data Reconciliation? A Practical Guide

Data reconciliation is the process of comparing two sets of records to verify they agree. You take data from two sources — a bank statement and a ledger, an invoice list and a payment log, two copies of a customer database — and check whether they match.

If they don't, you figure out why.

Why reconciliation matters

Every business that handles money, inventory, or customer data does some form of reconciliation. The goal is simple: catch errors before they become problems.

Without reconciliation, you might:

• Pay an invoice twice because it appeared in two systems with different reference numbers
• Miss a payment that was recorded in one system but not the other
• Report incorrect revenue because two reports disagree on the totals
• Ship the wrong quantity because warehouse counts don't match system records

Reconciliation is how you find these gaps while they're still fixable.

What reconciliation actually involves

The process breaks down into three steps:

1. Matching

Find records that appear in both data sets. This usually means matching on a shared identifier — a transaction ID, invoice number, employee ID, or order number.

Sometimes a single field isn't enough. You might need to match on a combination of fields, like customer name AND invoice date, to uniquely identify a row.

2. Comparing

Once rows are matched, check whether the details agree. Does the amount in File A match the amount in File B? Do the dates line up? Are the statuses the same?

This is where most problems surface. Two records might share the same ID but disagree on the dollar amount, the date, or a status field.

3. Identifying gaps

Find records that exist in one data set but not the other. A transaction in your ledger with no corresponding entry on the bank statement. An invoice you sent that never shows up in the payment log.

These gaps are often the most important findings, because they represent things that fell through the cracks.

Common types of reconciliation

Bank reconciliation

Comparing your internal accounting records against bank statements. The goal: make sure every transaction in your books matches a real bank transaction, and vice versa.

This is typically done monthly. Discrepancies might indicate recording errors, timing differences (checks that haven't cleared), or unauthorized transactions.

Intercompany reconciliation

When two entities within the same organization transact with each other, both sides need to record the same amounts. Intercompany reconciliation verifies that Entity A's payable matches Entity B's receivable.

Vendor reconciliation

Comparing your purchase orders against vendor invoices and payment records. Catches duplicate invoices, missed payments, and pricing discrepancies.

Inventory reconciliation

Matching physical stock counts against system records. Identifies shrinkage, miscounts, and data entry errors.

Data migration reconciliation

After moving data from one system to another, reconciliation verifies that every record transferred correctly. This catches rows that were dropped, fields that were truncated, or values that were transformed incorrectly.

The manual approach (and its limits)

The traditional way to reconcile in Excel:

1. Open both files
2. Write VLOOKUP or INDEX-MATCH formulas to find matching IDs
3. Add more formulas to compare values across matched rows
4. Filter for #N/A to find unmatched rows
5. Manually review and investigate discrepancies

This works for small, simple files. But it breaks down when:

• Your files have thousands of rows and Excel slows to a crawl
• You need to match on multiple columns
• Names are spelled slightly differently between systems ("Jon Smith" vs "John Smith")
• Amounts differ by fractions of a cent due to rounding
• You need to hand off the process to a colleague who doesn't know the formulas

A faster approach

SheetPair automates the entire reconciliation process:

1. Upload both files (CSV or Excel)
2. Map key columns (for matching) and compare columns (for checking differences)
3. Get results: matched rows, rows with differences highlighted at the cell level, and rows missing from each file

It handles fuzzy matching for name variations, numeric tolerance for rounding differences, and supports multiple key columns. Everything processes in your browser, so your data stays private.

Free for files up to 1,000 rows — no account needed.

How often should you reconcile?

It depends on the stakes:

Type	Typical frequency
Bank reconciliation	Monthly (some companies do daily)
Accounts payable/receivable	Weekly or monthly
Intercompany	Monthly or quarterly
Inventory	Monthly, quarterly, or annually
Data migration	Once, immediately after migration
Ad-hoc data checks	As needed

More frequent reconciliation catches problems earlier. If you only reconcile quarterly, a mistake in January might not surface until April.

Key terms

• Key column — the field used to match rows between two data sets (e.g., Transaction ID, Invoice Number)
• Tolerance — an acceptable margin of difference, usually for numeric fields (e.g., amounts within $0.01 are treated as matching)
• Break — a discrepancy between matched records (also called an exception or variance)
• Unmatched item — a record that exists in one data set but has no counterpart in the other

Try it

If you're currently reconciling with VLOOKUP or manual review, try SheetPair. Upload two files, map your columns, and see results in seconds.