August 22, 2026 9 min read

How to Reconcile Two Spreadsheets When the Data Doesn't Match

Learn how to compare and reconcile two spreadsheets when rows, identifiers, values, or structures do not match—and how to investigate discrepancies before changing the data.

reconciliation
excel
power-query
data-quality
investigation
spreadsheets

Two spreadsheets are supposed to describe the same business reality.

They don’t.

One says an invoice was paid. The other says it is still outstanding.

One calls the customer “Northstar Ltd.” The other says “Northstar Limited.”

One file contains 4,812 rows. The other contains 4,779.

At that point, comparing cells is the easy part.

The hard part is deciding which differences actually matter.

Reconciliation is not the process of making two spreadsheets identical. It is the process of explaining why they are different.

A mismatch may represent a real error. It may also represent timing differences, different source-system rules, different field definitions, different levels of detail, missing records, legitimate historical events, or genuinely contradictory data.

Difference is not automatically an error.

Spreadsheet operators repeatedly ask how to compare two sheets when rows are not in the same order, how to match transactions when identifiers can repeat, how to find records that exist in one file but not the other, and what to do when headers and values disagree. Those are practitioner problems — not proof that any one forum answer is correct. The method below is what holds up when you treat reconciliation as investigation.

Start by defining what the two spreadsheets represent

Before matching anything, answer:

What does one row represent in File A?
What does one row represent in File B?
Are both files snapshots?
Is one file historical or event-based?
Were they exported at the same point in time?
Are currencies and units the same?
Are totals gross or net?
Are dates created dates, payment dates, shipment dates, or update dates?

Call this the record grain: what one row actually represents.

Example:

File A: one row per order
File B: one row per order event

Repeated Order IDs in File B can be legitimate. Matching those files as if every Order ID should appear once will invent false problems.

Do not attempt one-to-one row matching until the grain of both datasets is understood.

This is the same caution that applies when repeated identifiers look like duplicates but may simply be history.

Do not compare spreadsheets by row position

Row 152 in File A has no reason to correspond to Row 152 in File B.

That is especially true when:

rows were sorted differently
one file contains extra records
records are missing
exports were created at different times
filters were applied
systems use different ordering

Match records using business identity, not spreadsheet position.

Possible keys:

Customer ID
Invoice number
Transaction ID
Order ID
SKU
Account number

One column is not always enough.

An account number can appear many times across a statement period. Matching on Account Number alone can pair the wrong transactions. In those cases, a composite key may be required — for example:

Account Number + Transaction Amount + Date
Order ID + Line Number

A composite key is appropriate only when those fields together actually identify a record in the business process.

Do not concatenate columns merely because a formula tutorial suggested it. Invented keys create invented matches.

What if the column names or structures are different?

File A	File B
Customer ID	Client Number
Invoice Amount	Amount Paid
Payment Date	Settlement Date

Corresponding concepts can wear different labels.

Reconciliation may require:

mapping equivalent fields
confirming data types
confirming units and currency
confirming date semantics
confirming whether the fields actually mean the same thing

Similar labels do not prove identical meaning.

“Amount” could mean gross amount, net amount, balance, paid amount, or invoice amount. Guessing creates false confidence.

Once the mapping and transformation rules are known, Power Query is often a strong choice for repeatable joins and reshapes. Microsoft’s Power Query guidance emphasizes correct data types, modular queries, documenting steps, reusable functions, and designing queries that tolerate changing source structures. That advice is about maintainable transformation — not about deciding which conflicting value is true.

Classify the differences before trying to fix them

After records are matched on a defensible key, put every difference into a class before changing anything.

Class	Meaning
Exact match	Records correspond and relevant fields agree
Candidate match after harmless normalization	Looks equivalent after formatting or naming cleanup — still a candidate, not proven identity
Expected difference	Explained by timing, scope, or known process rules
Missing from one source	Present in one file, absent in the other — reason unknown until investigated
Unexplained discrepancy	Clear correspondence, conflicting values, no known reason
Conflicting authoritative evidence	Two supposedly authoritative sources disagree

Exact match

Nothing to fix. Useful as a baseline for confidence in the matching logic.

Candidate match after harmless normalization

Examples:

Northstar Ltd / Northstar Limited
01/08/2026 / 2026-08-01

These may refer to the same entity or date. They are candidate matches.

Normalization can make comparison easier. It does not automatically prove two records are the same entity. Where ambiguity remains, confirm identity with additional evidence.

Expected difference

File A exported at 09:00. File B exported at 15:00. A payment posted at noon.

Both files can be correct for their timestamps. Forcing them to match would erase a legitimate timing difference.

Missing from one source

Possible explanations:

different export windows
filters
integration failure
deletion
newly created record
scope difference

Do not automatically call it an omission.

Unexplained discrepancy

Records clearly correspond, but one or more values disagree and there is no known reason.

Needs investigation.

Conflicting authoritative evidence

Two systems that are each treated as authoritative disagree.

Do not auto-fix this. Escalate or gather more evidence.

What happens when both values look plausible?

Invoice	File A	File B
INV-9182	$4,850	$4,805

Do not average them.

Do not arbitrarily select one.

Investigate:

Which system is authoritative for this field?
Were both exports generated at the same time?
Is one gross and one net?
Are fees included in one source?
Are currencies identical?
Was a correction made after one export?
Is there a known tolerance?
Is there a third source of evidence?

Source of truth is often field-specific

There is not always one master system for the whole row.

Examples:

CRM → account owner
Billing platform → payment status
Warehouse system → stock quantity

The authoritative source may differ by field. Choosing “the newer export” as a universal winner is a shortcut, not a method. The same caution applies when verifying contact data across sources before CRM import.

Tolerances are business rules, not universal truths

Two monetary values may differ because of:

rounding
tax
currency conversion
fees
timing

Some businesses define an acceptable tolerance — for example ±$0.01, or a percentage threshold defined by accounting policy.

There is no universal “close enough” rule that software should invent for you.

Tolerance must come from business rules, accounting policy, system behavior, or domain context.

A tool should not silently decide that a small difference means the records are correct.

A practical spreadsheet reconciliation workflow

1. Preserve both originals

Do not overwrite source files. Work on copies or query layers.

2. Establish the grain of each dataset

What does one row represent?

3. Confirm scope and timing

Are the files supposed to cover the same period, customers, transactions, geography, systems, and status range?

4. Map corresponding fields

Confirm meaning, data type, units, date semantics, and currency.

5. Establish reliable matching logic

Use stable IDs, composite identifiers, or business keys.

Avoid row-position matching.

6. Normalize only harmless representation differences

Whitespace, letter case, date representation, and known company-suffix conventions can help comparison.

Normalization assists comparison. It does not automatically prove two records are the same entity.

7. Compare corresponding records

Identify matches, missing records, conflicting values, unmatched records, and unexpected duplicates.

8. Classify discrepancies

Use the categories above.

9. Investigate unresolved differences

Possible evidence:

source-system history
timestamps
audit logs
invoices
CRM history
transaction records
business documentation
an authoritative third source

10. Resolve only what evidence supports

Correct what is actually wrong.

Leave legitimate differences intact.

Escalate uncertainty.

11. Verify the result

After changes, rerun the comparison.

Confirm the patch did not create new discrepancies.

A compact version of the same chain:

Match → Classify → Evidence → Resolve / Leave / Escalate → Verify

When Excel or Power Query may be enough

Be fair.

For small or well-understood reconciliations, Excel may be sufficient using:

XLOOKUP
INDEX/MATCH
COUNTIFS
conditional formatting
helper columns

These help you find corresponding records and surface mismatches. They do not decide which value is true.

One practical caution: classic lookup functions return the first match they find. If an identifier repeats and the grain is wrong, the first match can be the wrong match. That is another reason grain and matching keys come before formulas.

For repeatable joins and transformations, Power Query is often excellent — especially once mappings are stable and the same two exports arrive every week.

Auditere is not a replacement for Excel or Power Query. For tool choice by job type, see Excel, Power Query, OpenRefine, CRM, or Auditere?.

When reconciliation becomes an investigation problem

The problem changes when:

thousands of records need review
matching is ambiguous
multiple fields conflict
there are unexplained missing records
one system is not universally authoritative
historical differences matter
the operator must understand why a value was flagged
corrections need to be deliberate and reversible

At that point, the problem is no longer simply comparing spreadsheets.

It is investigating competing versions of reality.

That is closer to finding bad data and auditing before you trust a file than to running a merge and calling the work done.

Where Auditere fits — and where it does not

If the reconciliation is small and deterministic, Excel or Power Query may be all you need.

Auditere is designed for cases where discrepancies themselves need investigation: finding questionable records, understanding why they were flagged, reviewing available evidence, resolving supported issues deliberately, and verifying the resulting data.

This article is not claiming that Auditere is a complete two-file bank-reconciliation product, or that you should upload two exports and let software force them into agreement.

Making two files identical is easy.

Explaining why they differ — and changing only what the evidence supports — is the real work.

Auditere is live for beta. Start your free trial or view pricing — only if investigation, not forced agreement, is the bottleneck.