How to compare two Google Sheets for differences
By Jake Marsh · Updated
To compare two Google Sheets for differences, open Extensions, Tabmeld, Compare sheets, pick the old table and the new table, add the column that identifies a row (such as ID or Email) under Match rows by, and click Create report. You get a report tab with every new, removed and changed row, the changed cells colored, and a "What changed" note such as Price: 10 → 12.
Why rows need a key
Comparing cell A2 with cell A2 only works if both tables have the same rows in the same order. Real exports don't: rows get added, deleted and re-sorted. A key column tells Tabmeld which row in the old table is the same thing as a row in the new one, wherever it sits. Use one column when it's unique, like an order number or email, or several together, like First name + Last name + Date of birth.
Steps
- Open Compare sheets from the Tabmeld menu.
- Pick the two tables under Tables: any tab in this spreadsheet, or a tab in another file through Pick a file from Drive.... Set Headers are in row if your header isn't row 1.
- Add the key under Match rows by with Add a key column. Columns are matched by header name, so the two tables can have their columns in a different order.
- Choose what to compare under Compare these columns. Columns with the same header in both tables are paired for you.
- Set the matching options if the data is messy: Ignore upper/lower case, Ignore extra spaces, Ignore accents, Ignore punctuation, and Numbers within a tolerance so 10.001 and 10 count as equal.
- Pick the report sections: New rows, Removed rows, Changed rows and, if you want them, Unchanged rows.
- Click Preview for the counts, then Create report.
What the report shows
| Section | Means |
|---|---|
| New rows | The key is only in the new table |
| Removed rows | The key is only in the old table |
| Changed rows | Same key, at least one compared cell differs; those cells are colored and listed in What changed |
| Unchanged rows | Same key, nothing differs |
A summary tab has the counts. Tick Also highlight in the tables to color the rows in the original tabs too; Tabmeld makes a backup tab first.
When a key was edited
If someone fixed a name from "Jon Smith" to "John Smith", an exact comparison lists one removed row and one new row. Tick Close matches and set Closeness (85% by default) to pair those rows as one changed row instead. For matching two different lists by similar names, rather than two versions of one list, Fuzzwell is built for that.
Compare again next week
The report tab remembers how it was made. Click Refresh in the sidebar after the new export arrives and the report is rebuilt from the current data.
What it costs
Tabmeld is $4.99 a month or $39 a year per Google account. Free trial: 14 days or 20 runs, whichever comes first. No card needed. Every plan and the install link are on the Tabmeld page.
Tabmeld: Combine, Merge & Compare Sheets
Combine, consolidate, compare and merge Google Sheets tabs and files in one add-on $4.99 a month or $39 a year per Google account.
Questions
Can I compare tabs in two different spreadsheets?
Yes. Pick either table from another file in Google's file picker. Tabmeld reads it and never changes it.
What if the key appears twice in one table?
Repeats are matched in order: the first in the old table with the first in the new one, and so on. Rows with a blank key are left out of matching.
Does comparing change my data?
No, unless you tick Also highlight in the tables, and then a backup tab is made first.
How large can the tables be?
A run reads up to 3,000,000 cells. Close matching stops at 2,000,000 row comparisons, and the report says so if it got there.
More Tabmeld guides
- How to consolidate data from multiple sheets in Google Sheets
- How to update one Google Sheet from another by a key column
More Google guides
- How to move GA4 Reports Builder reports to Trafficledger (Trafficledger)
- How to make a copy of a template tab for each name in a list (Tabherd)
- How to email each person only their rows from a Google Sheet (Scheduled Sheet Reports)
- How to show dotted-line reporting on an org chart from Google Sheets (Rowtree)