Compare two Google Sheets™ tabs by key
Two exports can contain the same records in a different order. Comparing row 2 with row 2 then reports changes that are only sorting differences. Match each record by a stable unique ID first, then compare the fields you care about.
For a small, regular list, native formulas or a free comparison add-on may be enough. DiffFill provides an explicit mapping, free preview and a separate report tab without editing either source.
A small inventory example
Download the example CSV files and instructions. Import them into one spreadsheet as tabs named Before and After. Keep the headers; when importing, turn off conversion of text to numbers, dates and formulas. The example uses synthetic records only.
Before, range A1:C4:
| ID | Name | Stock |
|---|---|---|
| ITEM-001 | Blue mug | 12 |
| ITEM-002 | Tea tin | 5 |
| ITEM-003 | Canvas bag | 8 |
After, range A1:C4:
| Stock | Item | Product |
|---|---|---|
| 8 | ITEM-003 | Canvas bag |
| 4 | ITEM-004 | Desk pad |
| 7 | ITEM-001 | Blue mug |
The new export reordered both records and columns. ITEM-003 still matches. ITEM-001 changed
stock from 12 to 7. ITEM-002 was removed and ITEM-004 was added.
Choose the keys and map the columns
- Open Extensions > DiffFill: Compare Two Tabs for Google Sheets™ > Open DiffFill and check the Google account shown. Its free allowance and purchase apply to that account. If several accounts are signed in, use a window signed into only your intended account.
- Choose Before as the old tab and After as the new tab, both
A1:C4. Leave First row of both ranges contains headings checked. Set the old key to Column A and new key to Column B. - Map the first compared field: report label Name, old Column B, new Column C. Choose Add compared field and map Stock, old Column C, new Column A. Keys identify records; do not map them again as compared fields.
- Choose Preview comparison - free. The expected result is 1 added, 1 removed, 1 changed and 1 matching row, with 5 field detail lines. Added and removed records each have two details because you mapped two fields; the changed record has one changed field.
- Browse details and confirm I reviewed this comparison before Create or reopen this report. Open the resulting report tab and follow its source-row links when checking a result. Your original tabs stay unchanged; the report follows the spreadsheet’s existing sharing.
A new report is a snapshot. DiffFill does not merge changes into the lists or update a report when a source changes. Preview is free; three generated reports in total per Google account are free, then further reports within supported limits cost $9 once. No subscription. A recognized retry of the unchanged latest report uses no additional free report. A new zero-difference report still counts. See DiffFill’s supported limits and offer.
Decide what should count as different
DiffFill compares exact displayed text. Case, significant spaces and leading zeroes still
visible in the source matter. ITEM-001 and item-001 are different keys; 00123 and 123 are
also different. Empty or whitespace-only keys and duplicate keys refuse the entire comparison.
Choose a unique record ID rather than a customer name that several rows can share.
A date displayed as 19/10/2026 differs from 2026-10-19, even if the underlying date is equal.
Different formulas showing the same result match. DiffFill does not audit formula definitions,
formatting, notes or validation rules. If you want dates or numbers normalized, align their display
before previewing. A leading zero already lost during import cannot be recovered by comparison.
The free formula route for this example
If maintaining helper columns suits the job, Google’s
XLOOKUP can retrieve stock by ID. In a
working copy of Before, put this in D2 and fill down through D4:
=XLOOKUP(A2,After!B$2:B$4,After!A$2:A$4,"Missing",0)
The final 0 requests an exact match. This returns 7, Missing, 8 for the example’s three
records. Compare column C with D to see changed stock; Missing marks a removed ID. Choose a
missing marker that cannot also be a real stock value. The formula returns the first matching
record, so check that your keys are unique.
To flag additions in a working copy of After, put this in D2 and fill down:
=IF(COUNTIF(Before!A$2:A$4,B2)=0,"Added","Existing")
It returns Existing, Added, Existing. Google documents that
COUNTIF ignores case and recognizes
wildcards. These simple helpers suit the example’s consistent ITEM- IDs; they are not a
replacement for case-sensitive, literal-key comparison. For character-for-character checks,
Google’s EXACT is case-sensitive. Different
comparison rules and additional fields need their own formulas and validation.
SheetDiff is another real option: its own page advertises five comparisons a day, up to 500 rows, free. That can be more generous for small repeated jobs than DiffFill’s three reports total. Choose based on the workflow and limits you need; comparison does not require paying for an add-on.