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

  1. Open Compare sheets from the Tabmeld menu.
  2. 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.
  3. 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.
  4. Choose what to compare under Compare these columns. Columns with the same header in both tables are paired for you.
  5. 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.
  6. Pick the report sections: New rows, Removed rows, Changed rows and, if you want them, Unchanged rows.
  7. Click Preview for the counts, then Create report.

What the report shows

SectionMeans
New rowsThe key is only in the new table
Removed rowsThe key is only in the old table
Changed rowsSame key, at least one compared cell differs; those cells are colored and listed in What changed
Unchanged rowsSame 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


More Google guides