How to compare two spreadsheets for differences and matches
By the Analistable team · Updated · 3 min read
To compare two spreadsheets, first decide what “different” means. For cell-by-cell changes in sheets with the same layout, use =IF(Sheet1!A1<>Sheet2!A1,"Changed","") or conditional formatting. For rows missing from one list, use COUNTIF or XMATCH on a key column. For a full added / removed / changed report, use Power Query Merge with anti joins, or our free spreadsheet compare tool.
Three kinds of comparison
| Question | Comparison | Best method |
|---|---|---|
| Which cells changed between two versions? | Cell by cell, same layout | IF formula or conditional formatting |
| Which customers are in list A but not list B? | By key column | COUNTIF / XMATCH or an anti join |
| What was added, removed and changed? | By key, then by value | Power Query Merge or a compare tool |
Compare two sheets cell by cell
When both sheets have the same rows and columns in the same order (two versions of a report), add a third sheet and enter in A1:
=IF(Old!A1<>New!A1, Old!A1&" → "&New!A1, "")
Fill it across and down to cover the range. Every non-blank cell is a change. Conditional formatting does the same visually: select the range on the New sheet, choose Home → Conditional Formatting → New Rule → Use a formula, enter =A1<>Old!A1 and pick a fill colour. Full walkthrough: comparing two Excel sheets for differences.
Compare two lists by a key column
If rows can be in a different order, compare by an ID instead of by position. In a helper column next to list A:
=IF(COUNTIF(ListB!A:A, A2)=0, "Missing from B", "In both")
Swap the ranges to find rows missing from A. For two columns on the same sheet, see comparing columns in Excel, and for matches between lists, finding matches between two lists.
A full added / removed / changed report
Power Query's Merge Queries can produce each part of a reconciliation:
- Left anti join (Old → New): rows that were removed.
- Right anti join: rows that were added.
- Inner join, then a custom column comparing each field: rows that changed.
This is repeatable: load next month's versions and click Refresh.
Duplicates are a comparison too
Finding repeated rows inside one list, or merging two lists without repeats, uses the same matching logic. See combining duplicate rows and merging two spreadsheets and removing duplicates.
Common reasons comparisons go wrong
- Trailing spaces and non-printing characters make identical-looking values differ. Clean with TRIM and CLEAN.
- Numbers stored as text never equal real numbers. Convert one side with VALUE or Text to Columns.
- Rounding: 0.1+0.2 isn't exactly 0.3. Compare with ROUND when values are calculated.
- Rows inserted in one version shift a cell-by-cell comparison. Compare by key instead.
Which method for which situation
| Situation | Start with | Then, if needed |
|---|---|---|
| Two versions of the same report | Difference formula or conditional formatting | Spreadsheet Compare for formulas and formatting |
| Two exports of a customer or product list | COUNTIF / XMATCH on the ID column | Power Query anti joins for a refreshable report |
| Monthly reconciliation (bank vs ledger) | Power Query Merge on date + amount | A tolerance column for small differences |
| Two lists with spelling differences | Clean with TRIM, LOWER and SUBSTITUTE | Power Query fuzzy merge |
Whatever the method, write down what the result should be before you start — for example “every invoice in the ledger should appear in the bank export within 3 days”. It turns a vague comparison into a check you can verify.
A worked reconciliation
Last month's customer list (Old) against this month's (New), matched on Customer ID:
| Customer ID | Old: Plan | New: Plan | Status |
|---|---|---|---|
| C-101 | Pro | Pro | Unchanged |
| C-102 | Basic | Removed | |
| C-103 | Basic | Pro | Changed: plan |
| C-104 | Basic | Added |
Four rows, three kinds of change. A cell-by-cell comparison of the same two sheets would report almost every row as different, because C-102's removal shifts everything below it up by one row.
Every guide in this topic
- How to combine duplicate rows in Excel
Merge duplicate rows in Excel into one row per value: sum numbers with a pivot table or GROUPBY, join text with TEXTJOIN, or use Power Query Group By.
- How to compare two columns in Excel
Compare two columns in Excel row by row or as lists: = and EXACT, Ctrl+\ for row differences, conditional formatting, COUNTIF and XMATCH to find missing values.
- How to compare two Excel sheets for differences
Find every difference between two Excel sheets: a difference formula, conditional formatting, View Side by Side, Spreadsheet Compare and key-based checks.
- How to find matches between two lists in Excel
Find values that appear in both lists, or only in one, with COUNTIF, MATCH and ISNUMBER, XMATCH and FILTER — across columns, sheets or workbooks.
- How to merge two spreadsheets and remove duplicates
Combine two Excel lists into one without duplicates: UNIQUE and VSTACK, Remove Duplicates, keeping the newest row, and a refreshable Power Query version.
- The best tools to compare two spreadsheets
Excel formulas, Spreadsheet Compare, Power Query, diff tools, add-ins and browser tools for comparing two spreadsheets: what each finds and its limits.
Frequently asked questions
- How do I compare two Excel sheets for differences?
- If the layout is identical, use =IF(Sheet1!A1<>Sheet2!A1,"Changed","") on a third sheet or a conditional formatting rule. If rows can move, compare by a key column with COUNTIF or XMATCH.
- Does Excel have a built-in compare feature?
- Windows editions with Microsoft 365 Apps for enterprise or Office Professional Plus include Spreadsheet Compare (Inquire). Other editions can use View Side by Side, formulas or Power Query.
- How do I find rows that are in one sheet but not the other?
- Add =COUNTIF(OtherSheet!A:A, A2)=0 next to your list. TRUE means the row is missing from the other sheet. A Power Query left anti join gives the same result as a table.
- Can I compare two spreadsheets online without uploading them?
- Yes. Analistable's compare tool runs in your browser, so both files stay on your computer.