Blog · Data quality and reconciliation · Finance and FP&A teams
Match two business exports by a stable key and separate missing records from changed amounts. Use exact-match formulas, duplicate checks and a reconciliation that explains the full difference between the source totals.
Data comparison in Excel means matching the same business records across two exports and explaining every missing record or changed amount. Use a stable key, test it for duplicates, compare both directions and reconcile the exceptions to the difference between the source totals.
Comparing cells in the same row is reliable only if row order and membership never change. Most business exports need a record-level comparison because sorting, new transactions and removed records change their positions.
Write down the scope before adding formulas: the company, currency, cutoff date, amount definition and row meaning. Two files cannot reconcile cleanly if one contains posted invoices and the other includes drafts.
Choose the identifier for that row meaning. An invoice ID can identify an invoice summary, but an invoice-line export may need an invoice ID plus a line number. Customer names are poor substitutes because names can be edited or shared by different accounts.
Keep identifiers as text when leading zeros matter. Review spaces, blanks and inconsistent types before matching; the guide to Excel data cleaning tools covers those choices. Preserve the original identifiers so a reviewer can trace any normalization.
Also define the direction of the difference. This example always uses revised minus original. A negative difference therefore means the revised file is smaller.
A US distributor compares an original invoice summary with a revised summary for the same closed period. Both contain USD invoice amounts after credits. Each export should contain one row per invoice.
Place the original data in A1:B5 on a sheet named Original, and the revised data in A1:B5 on a sheet named Revised. The exports are shown side by side below; their row positions do not establish a match.
| Original invoice | Original amount (USD) | Revised invoice | Revised amount (USD) |
|---|---|---|---|
| A101 | 12,000 | A101 | 12,000 |
| A102 | 8,500 | A102 | 8,200 |
| A103 | 6,000 | A104 | 3,500 |
| A104 | 3,500 | A105 | 2,400 |
| Total | 30,000 | Total | 26,100 |
The revised total is $3,900 below the original: $26,100 minus $30,000. That is the control difference to explain, not the explanation itself.
=SUM(Revised!B2:B5)-SUM(Original!B2:B5)
Do not change either source sheet while investigating. Corrections belong in a new source version, with the reason recorded in the team's reconciliation record.
On a Compare sheet, put the combined distinct invoice keys in A2:A6: A101, A102, A103, A104 and A105. For a manual comparison, copy both key columns into a separate staging area, review them and produce a distinct list without altering the source sheets.
Checking only the original list would miss A105, which appears only in the revised export. The combined list contains five keys while each source contains four records; that is expected because the files have different membership.
Use these columns on Compare:
| Column | Purpose |
|---|---|
| A | Combined invoice key |
| B | Count in original export |
| C | Count in revised export |
| D | Original amount, blank if absent |
| E | Revised amount, blank if absent |
| F | Difference for a matched pair |
| G | Record status |
| H | Signed contribution to the total difference |
In B2 and C2, enter these formulas and fill down:
=COUNTIF(Original!$A$2:$A$5,A2)
=COUNTIF(Revised!$A$2:$A$5,A2)
Microsoft documents COUNTIF as a count of cells meeting a criterion. It also notes that text matching is case-insensitive and supports wildcards, so confirm that your identifier rules suit that behavior. The simple invoice keys here contain no wildcard characters.
Every count must be zero or one. If a count exceeds one, stop and investigate whether the key is incomplete or the source contains duplicates. A first matching amount cannot settle that question.
After uniqueness checks pass, enter the original and revised lookups in D2 and E2:
=IF(B2=0,"",XLOOKUP(A2,Original!$A$2:$A$5,Original!$B$2:$B$5,"Missing",0))
=IF(C2=0,"",XLOOKUP(A2,Revised!$A$2:$A$5,Revised!$B$2:$B$5,"Missing",0))
The final zero requests an exact match. Microsoft's XLOOKUP documentation explains the match options and that the function returns the first match it finds. It is unavailable in Excel 2016 and Excel 2019, so confirm the workbook version before using these formulas.
Covirage is not affiliated with Microsoft.
In F2, compare amounts only when the key occurs once on both sides:
=IF(AND(B2=1,C2=1),E2-D2,"")
In G2, classify the record:
=IF(B2=0,"Only revised",IF(C2=0,"Only original",IF(F2=0,"Equal","Amount changed")))
In H2, calculate the contribution to the bridge between totals:
=IF(C2=1,E2,0)-IF(B2=1,D2,0)
Fill the formulas down through row 6. Missing records stay visible as a status, even though the bridge treats the absent side as zero for arithmetic.
| Invoice | Original amount (USD) | Revised amount (USD) | Status | Bridge contribution (USD) |
|---|---|---|---|---|
| A101 | 12,000 | 12,000 | Equal | 0 |
| A102 | 8,500 | 8,200 | Amount changed | -300 |
| A103 | 6,000 | — | Only original | -6,000 |
| A104 | 3,500 | 3,500 | Equal | 0 |
| A105 | — | 2,400 | Only revised | 2,400 |
| Total | 30,000 | 26,100 | -3,900 |
The three common keys contain two equal amounts and one changed amount. The remaining keys explain records absent from one side.
The amount change of -$300, original-only amount of -$6,000 and revised-only amount of $2,400 sum to -$3,900. That equals the source difference exactly.
=SUM(H2:H6)-(SUM(Revised!B2:B5)-SUM(Original!B2:B5))
The check must return zero. Also check the counts: common keys plus original-only keys must reproduce the original record count, and common keys plus revised-only keys must reproduce the revised count.
Amounts in this example are whole dollars, with no rounding adjustment. For files stored in cents, compare the original precision and document any tolerance separately. A tolerance labels a small difference; it should not erase it from the bridge.
Assign each exception to an owner who can explain its source. A missing invoice could reflect a cutoff change, a cancellation or an incomplete export. The status identifies what differs; it does not establish why. When the amounts agree, visualizing the checked data can make the business change easier to communicate.
For decisions about keeping a recurring process in a workbook, see Excel or an analytics tool. The need for a documented key, scope and control total remains the same.
Bring a workbook containing your original and revised exports to Covirage, with the key and amount columns identified. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See Excel analysis for the supporting business workflow.
Only when both files have exactly the same records in exactly the same order, which is a fragile assumption. Sorting or inserting a row breaks that comparison. A stable invoice, order or account identifier lets you match the business record even when its position changes.
A lookup returns a matching value; it does not prove that the key appears only once or that every key in the other file was reviewed. Count key occurrences first and compare a combined key list so duplicates and records found only in the second export remain visible.
Keep missing records distinct from records with a genuine zero amount. You can use zero for the absent side when calculating a signed bridge between totals, but retain a separate status showing which export lacks the record. That preserves the meaning of the exception.