How to Compare Two Excel Sheets and Find What's Different

Two lists that should line up but don't — a CRM export against your accounting system, this month against last, planned against actual. Here's how to find what's missing, spot where the numbers disagree, and reconcile the two without doing it by eye.

Comparing two spreadsheets by scrolling is how errors ship. You need two answers: what's in one and not the other, and where the same row disagrees on a value. I'll walk through the real Excel methods — COUNTIF, XLOOKUP, conditional formatting, variance — the things that make "identical" data refuse to match, and a faster way to just get the list of differences.

The fast way: ask for the differences

If you've got the Quiriz add-in in Excel, you can skip the scaffolding and ask for the mismatched rows the way you'd say it out loud:

=QUIRIZ.ASK("which invoice numbers are in the Tally export but missing from the Odoo export", "table")

The "table" mode spills the answer as rows — the actual missing IDs, not a column of TRUE/FALSE you still have to filter. Want the value mismatches instead?

=QUIRIZ.ASK("show invoices where the Tally amount and Odoo amount don't match, with both amounts", "table")

It matches on the key, compares the values, and returns only the rows that disagree. That's the shortcut. Now the do-it-yourself version, which is worth knowing either way.

1. "Is this row in the other sheet?" — COUNTIF

The simplest check. In Sheet1, next to each ID, ask whether it exists in Sheet2:

=IF(COUNTIF(Sheet2!A:A, A2) = 0, "Missing in Sheet2", "Found")

Drag it down. Every "Missing in Sheet2" is a row the other sheet is missing. Then flip the references and run the same check on Sheet2 to catch what's missing the other way — comparisons have two directions, and people forget the second one.

2. "Do the values match?" — XLOOKUP

Existence isn't enough when the amount differs. Pull the matching value from the other sheet:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

Then flag the disagreements directly — planned against actual, side by side:

=IF(B2 = XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B), "OK", "Mismatch")

On older Excel without XLOOKUP, INDEX/MATCH does the same job: =INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0)).

3. See differences at a glance — conditional formatting

When both sheets are the same shape and order, colour is faster than formulas. Select the range → Home → Conditional Formatting → New Rule → Use a formula → enter:

=A1 <> Sheet2!A1

Pick a fill colour, and every cell that differs lights up. The catch is in the setup: if the two sheets are in a different row order, this compares row 5 to row 5 and lies to you. Align them first.

4. Reconcile the totals (planned vs actual)

Once rows are matched, the gap is just subtraction:

=B2 - XLOOKUP(A2, Actual!A:A, Actual!B:B, 0)

Sum that column and you've got total variance. This is the everyday accounting reconciliation, done with one column of formula.

What quietly trips people up

When two sheets that "obviously" match won't, it's almost always the key column:

• Key formatting. 1001 as a number in one sheet, "1001" as text in the other — they never match. Standardize the key first.
• Trailing spaces and case. TRIM the keys; use EXACT if upper/lower case actually matters.
• Duplicate keys. XLOOKUP and MATCH return the first hit, so duplicates hide mismatches. De-dupe before you compare.
• Different column order breaks the conditional-formatting method entirely — line the columns up first.

Every one of these is a place a manual comparison goes wrong without telling you. If you'd rather not babysit the key column, the plain-English ask matches on meaning and hands back only what's different:

=QUIRIZ.ASK("rows in the CRM export with no match in the accounting export", "table")

Which should you use?

• A quick one-time check on two tidy sheets? COUNTIF and XLOOKUP are right there and cost nothing.
• Two messy exports from different systems, or a reconciliation you run every month? That's where asking for the difference list saves the setup — and re-running it is just re-asking.

Frequently asked questions

Formula syntax shown targets Excel 365 / 2021. XLOOKUP isn't available in older versions — use INDEX/MATCH instead.