Analistable

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

Pick the comparison that matches your question
QuestionComparisonBest method
Which cells changed between two versions?Cell by cell, same layoutIF formula or conditional formatting
Which customers are in list A but not list B?By key columnCOUNTIF / XMATCH or an anti join
What was added, removed and changed?By key, then by valuePower 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:

  1. 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.
  2. Set the key column to the same data type in both queries, and trim it (Transform → Format → Trim).
  3. Select Old, choose Home → Merge Queries as New, click the key in both tables and pick Full Outer (all rows from both).
  4. 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.
  5. 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 and new customer lists
old_list.customer_idold_list.planold_list.mrrnew_list.customer_idnew_list.plannew_list.mrr
C-101Pro49C-101Pro49
C-102Basic19C-103Pro49
C-103Basic19C-104Basic19
C-105Pro49C-105Pro59
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;
Result
customer_idold_plannew_planold_mrrnew_mrrstatus
C-101ProPro4949Unchanged
C-102Basic19Removed
C-103BasicPro1949Changed
C-104Basic19Added
C-105ProPro4959Changed

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

Quick decision guide
SituationStart withThen, if needed
Two versions of the same reportDifference formula or conditional formattingSpreadsheet Compare for formulas and formatting
Two exports of a customer or product listCOUNTIF / XMATCH on the ID columnPower Query anti joins for a refreshable report
Monthly reconciliation (bank vs ledger)Power Query Merge on date + amountA tolerance column for small differences
Two lists with spelling differencesClean with TRIM, LOWER and SUBSTITUTEPower 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:

Comparison result
Customer IDOld: PlanNew: PlanStatus
C-101ProProUnchanged
C-102BasicRemoved
C-103BasicProChanged: plan
C-104BasicAdded

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

Symptoms, causes and fixes
SymptomLikely causeFix
Every row below a certain point shows as changedA row was inserted or deleted in one versionSwitch from a cell-by-cell to a key-based comparison
Values look identical but are flagged as differentTrailing spaces, non-breaking spaces or line breaksTRIM and CLEAN both sides; SUBSTITUTE(A2, CHAR(160), " ") for non-breaking spaces
IDs exist in both lists but COUNTIF says missingNumbers on one side, text on the otherConvert one column so both share a type
COUNTIF reports matches that aren't thereWildcard characters (* or ?) in the valuesUse XMATCH or MATCH with exact match, or escape with ~
Amounts differ by tiny fractionsFloating-point results of calculationsCompare ROUND(A2, 2) values, or use a tolerance
Dates never matchDate-time on one side, date only on the other, or text datesConvert both to real dates and drop the time part
Conditional formatting colours the wrong cellsRule written with absolute references or started from the wrong cellEdit 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:

  1. 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.
  2. Clean in the query, not by hand. Trim, set types and lowercase in Power Query steps so the same cleaning runs on every refresh.
  3. 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.
  4. 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

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.