How to compare two spreadsheets for differences and matches
By the Analistable team · Updated · 12 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.
Make the conditional formatting reference relative (A1, not $A$1), otherwise every cell is compared with Old!A1. Select the range starting from A1 before you create the rule so the reference lines up with the active cell. To ignore case, wrap both sides: =NOT(EXACT(LOWER(A1), LOWER(Old!A1))); to ignore differences under a penny in calculated amounts, use =ABS(A1-Old!A1)>0.005 on the numeric columns only.
If the two versions are in separate workbooks rather than separate tabs, open both and use View → View Side by Side with Synchronous Scrolling for a visual check, or copy the old version into a tab of the new workbook so the formulas don't depend on an external link. To summarise how many cells differ, =SUMPRODUCT(--(Old!A1:F200<>New!A1:F200)) returns the count in one cell, which is a useful first number before you look at the detail.
Cell-by-cell in Google Sheets
The difference formula works unchanged in Google Sheets. Conditional formatting is the catch: a custom formula rule can't refer to another tab directly, so =A1<>Old!A1 is rejected. Use INDIRECT to build the reference instead:
=A1<>INDIRECT("Old!"&ADDRESS(ROW(),COLUMN()))Apply it under Format → Conditional formatting → Custom formula is to the range on the New tab. ADDRESS turns the current row and column into a reference such as $C$7, so each cell is compared with the same position on the Old tab. For two versions of the same Google Sheet, you may not need formulas at all: File → Version history → See version history highlights edits between saved versions, which answers “who changed what” when the comparison is about one file over time rather than two separate exports.
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.
In Microsoft 365 you can list the missing rows in one formula instead of filtering a helper column. FILTER keeps the IDs from list A that XMATCH can't find in list B:
=FILTER(ListA!A2:A500, ISNA(XMATCH(ListA!A2:A500, ListB!A2:A500)), "None missing")Google Sheets accepts a similar formula with COUNTIF, which works on a whole column there: =FILTER(ListA!A2:A, ListA!A2:A<>"", COUNTIF(ListB!A2:A, ListA!A2:A)=0). Excel 2019 and earlier have no FILTER, so stay with the COUNTIF helper column and use Data → Filter to show only “Missing from B”.
Finding that a row exists on both sides is only half the job; you also want to know whether its values changed. Pull the old value next to the new one with =XLOOKUP(A2, Old!A:A, Old!C:C, "New row") and compare. When there are many columns, build a row signature on each side, =TEXTJOIN("|", FALSE, B2:F2), look up the old signature by ID, and flag rows where the two signatures differ. One column then tells you which rows changed, and you only inspect those field by field.
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.
You can also produce all three in a single query with a full outer join and one status column:
- Load both versions into Power Query (Data → Get Data → From File → From Workbook, or From Table/Range for tabs in the current file) and name the queries Old and New.
- Set the key column to the same data type in both queries, and trim it (Transform → Format → Trim).
- Select Old, choose Home → Merge Queries as New, click the key in both tables and pick Full Outer (all rows from both).
- Expand the New column and tick the key plus the fields you want to compare; they arrive as New.Customer ID, New.Plan and so on.
- Add a custom column (Add Column → Custom Column) that labels each row, then Close & Load.
if [Customer ID] = null then "Added"
else if [New.Customer ID] = null then "Removed"
else if [Plan] <> [New.Plan] or [MRR] <> [New.MRR] then "Changed"
else "Unchanged"This is repeatable: replace the two source files with next month's versions, keep the same file names or update the source step, and click Data → Refresh All. Filter the status column to hide Unchanged rows and you have a change report in seconds. For the different join kinds behind this, see Power Query merge join kinds.
The same report in SQL
If you work in SQL, a full outer join with a CASE expression does the whole comparison. Here is a second example, with a monthly value (MRR) as well as the plan, so a row can change in more than one field:
| old_list.customer_id | old_list.plan | old_list.mrr | new_list.customer_id | new_list.plan | new_list.mrr |
|---|---|---|---|---|---|
| C-101 | Pro | 49 | C-101 | Pro | 49 |
| C-102 | Basic | 19 | C-103 | Pro | 49 |
| C-103 | Basic | 19 | C-104 | Basic | 19 |
| C-105 | Pro | 49 | C-105 | Pro | 59 |
SELECT COALESCE(o.customer_id, n.customer_id) AS customer_id,
o.plan AS old_plan, n.plan AS new_plan,
o.mrr AS old_mrr, n.mrr AS new_mrr,
CASE
WHEN n.customer_id IS NULL THEN 'Removed'
WHEN o.customer_id IS NULL THEN 'Added'
WHEN o.plan IS DISTINCT FROM n.plan
OR o.mrr IS DISTINCT FROM n.mrr THEN 'Changed'
ELSE 'Unchanged'
END AS status
FROM old_list o
FULL OUTER JOIN new_list n ON n.customer_id = o.customer_id
ORDER BY customer_id;| customer_id | old_plan | new_plan | old_mrr | new_mrr | status |
|---|---|---|---|---|---|
| C-101 | Pro | Pro | 49 | 49 | Unchanged |
| C-102 | Basic | 19 | Removed | ||
| C-103 | Basic | Pro | 19 | 49 | Changed |
| C-104 | Basic | 19 | Added | ||
| C-105 | Pro | Pro | 49 | 59 | Changed |
The totals tie out: MRR goes from 136 to 176, a rise of 40, which is −19 for the removed customer, +19 for the added one, +30 for C-103's upgrade and +10 for C-105's price change. IS DISTINCT FROM treats two blanks as equal and a blank versus a value as different, which a plain <> does not. The query runs as written in DuckDB and in recent SQLite versions. For more join patterns, see SQL joins across multiple tables.
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.
Check for duplicates before any key-based comparison, not after. If customer C-103 appears twice in the new list, COUNTIF still says “In both”, XLOOKUP compares only the first copy, and a Power Query merge produces two rows for C-103, one of which may be reported as changed when it isn't. =COUNTIF(A:A, A2)>1 on each list flags the repeats; resolve them, or compare on a fuller key such as customer ID plus site, before you trust the added and removed counts.
Built-in compare tools in Excel and Google Sheets
Some Windows editions of Office include Spreadsheet Compare, a separate app that compares two workbooks and lists differences in values, formulas, names and formatting, colour-coded by type. In those editions you can also turn on the Inquire add-in (File → Options → Add-ins → COM Add-ins), which adds a Compare Files button. It is the right tool when you need to know whether formulas or formatting changed, not just values, because formula-based comparisons only see results.
Spreadsheet Compare works by position, so it has the same weakness as a difference formula: an inserted row makes everything below it look changed. It isn't available in Excel for Mac or Excel for the web. Google Sheets has no equivalent beyond version history, which compares versions of one file rather than two different files. For lists whose rows move, a key-based method is still needed; the best tools to compare two spreadsheets reviews the add-ins and standalone options.
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.
When there is no clean ID to compare on
Key-based comparison needs a column that identifies each row in both files. Many lists don't have one: a supplier's price list uses its own codes, an event sign-up sheet has names and emails typed by attendees. Three approaches, in order of preference:
- Build a composite key from columns that together identify a row, such as surname, postcode and date of birth, joined with a separator:
=LOWER(TRIM(B2))&"|"&SUBSTITUTE(C2," ",""). Build it the same way on both sides. - Use a mapping table when two systems use different codes for the same thing. A two-column sheet of their code and your code becomes the bridge, and the rows it can't map are your first list of exceptions.
- Fuzzy match names that are spelled differently, with Power Query's fuzzy merge and a similarity threshold, then review every fuzzy match by hand before treating it as the same record.
Whichever you choose, report the rows that matched by each route separately. An exact match on email and a 0.85 similarity match on company name deserve different levels of trust.
Troubleshooting comparisons
| Symptom | Likely cause | Fix |
|---|---|---|
| Every row below a certain point shows as changed | A row was inserted or deleted in one version | Switch from a cell-by-cell to a key-based comparison |
| Values look identical but are flagged as different | Trailing spaces, non-breaking spaces or line breaks | TRIM and CLEAN both sides; SUBSTITUTE(A2, CHAR(160), " ") for non-breaking spaces |
| IDs exist in both lists but COUNTIF says missing | Numbers on one side, text on the other | Convert one column so both share a type |
| COUNTIF reports matches that aren't there | Wildcard characters (* or ?) in the values | Use XMATCH or MATCH with exact match, or escape with ~ |
| Amounts differ by tiny fractions | Floating-point results of calculations | Compare ROUND(A2, 2) values, or use a tolerance |
| Dates never match | Date-time on one side, date only on the other, or text dates | Convert both to real dates and drop the time part |
| Conditional formatting colours the wrong cells | Rule written with absolute references or started from the wrong cell | Edit the rule to use A1-style relative references |
Repeating the comparison every month
Most comparisons come round again: this month's export against last month's, the supplier's price list against yours. Set it up once:
- Fix the inputs. Save each export with a predictable name (
customers_2026-09.xlsx) and keep the same columns in the same order; if the source system changes its export, adjust the cleaning steps once rather than every month. - Clean in the query, not by hand. Trim, set types and lowercase in Power Query steps so the same cleaning runs on every refresh.
- Keep the status column and a summary. A small pivot of the status column (Added, Removed, Changed, Unchanged) gives the headline numbers before anyone looks at rows.
- Archive the result. Save the change report with the month in the file name, so next month's surprises can be traced back.
For finance-style comparisons where amounts must agree within a tolerance and dates within a few days, the reconciling data in Excel guide goes further, with examples such as comparing two price lists and finding mismatched totals between two reports.
How to check the result
- Counts reconcile. Old rows − Removed + Added should equal New rows. In the SQL example, 4 − 1 + 1 = 4; in the worked reconciliation, 3 − 1 + 1 = 3.
- Totals reconcile. The change in any total column should equal the sum of the changes you found, as with the 136 to 176 MRR example above.
- No key appears twice in either input, or the counts above stop adding up.
- Spot-check one of each status against the source files by hand, especially one Unchanged row: a comparison that silently compares the wrong column reports everything as Unchanged.
Every guide in this topic
- Customer churn comparison template
Compare two customer lists to see churned, new, expanded and contracted accounts, with logo churn rate and net revenue retention. Free Excel download.
- Duplicate checker template
Free Excel duplicate checker: paste a list, and formulas clean the key, count repeats, mark the first occurrence to keep and summarise how many rows to remove.
- 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 compare two price lists
Compare two price lists by SKU — old vs new, supplier vs supplier, or shop vs supplier: price changes and %, new and discontinued items, cheapest supplier.
- How to do case-sensitive matching between two lists
Excel lookups ignore case, so ABC and abc match. Use EXACT with XLOOKUP, XMATCH or FILTER for case-sensitive matches, or decide to normalise case instead.
- 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 an old and new list and flag new, removed and changed rows
Combine last month's and this month's list with a full outer join and a status column marking each row new, removed, changed or unchanged.
- How to merge contact lists without duplicates
Combine Mailchimp, HubSpot, Klaviyo or LinkedIn lead exports into one list: normalise emails, pick which record wins, and never lose an unsubscribe.
- 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.
- Master customer list template
Free workbook that merges customer lists from your CRM, web shop and newsletter into one master list, deduplicated by email, with no macros.
- Price comparison template
Free supplier price comparison workbook: paste each supplier's price list, compare unit prices side by side and see the cheapest supplier per item.
- 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.
- Two-list comparison template
Free Excel template that compares two lists by key: flags rows in both, only in list A or only in list B, and spots names that changed between lists.
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.
- How do I compare two Google Sheets?
- Bring both into one spreadsheet (IMPORTRANGE if they are separate files), then use a difference formula, COUNTIF on a key column, or a conditional formatting rule with INDIRECT, since Sheets rules can't reference another tab directly.
- Why does my comparison show every row as different?
- A row was added or removed in one version, so a cell-by-cell comparison is offset from that point. Compare by a key column such as an ID instead of by position.