How to compare two Google Sheets tabs and find differences

Quick answer

If both tabs contain the same records in the same order, you can compare individual cells. If rows may be added, deleted, or reordered, match them by a unique key first. Otherwise, one moved row can make every row below it look different.

Method 1: Find IDs missing from the second tab

If column A contains a unique ID, add this formula beside the first data row in Sheet A and fill it down: =IF(COUNTIF('Sheet B'!A:A,A2)=0,"Only in A","Found"). Repeat in the other direction to find IDs that exist only in Sheet B.

Method 2: Check whether a value changed

To compare the value in column B for the same ID, use =IFERROR(IF(B2=XLOOKUP(A2,'Sheet B'!A:A,'Sheet B'!B:B),"Match","Changed"),"Missing"). This works for a focused check, but each additional column needs another formula and review step.

Method 3: Compare complete rows with SheetDiff

1. Install SheetDiff, then open the spreadsheet containing both tabs.
2. Choose Extensions → SheetDiff → Start.
3. Select Sheet A, Sheet B, and a key column shared by both tabs.
4. Click Compare to create a color-coded SheetDiff Report tab.

SheetDiff matches records by the key instead of row position, so reordered rows do not create false differences. It reports rows only in A, rows only in B, changed values, matching rows, and duplicate keys.

Which method should you use?

• Cell formula: best when row positions are identical and you need one quick check.
• COUNTIF or XLOOKUP: best for one or two fields with a reliable unique key.
• SheetDiff: best for repeated comparisons, multiple changed columns, moved rows, duplicate review, or a report other people can inspect.

What appears in the comparison report?

• Only in A: the key exists in the first tab but not the second.
• Only in B: the key exists in the second tab but not the first.
• Changed: the key exists in both tabs, but one or more values differ.
• Matching: the matched row contains the same compared values.
• Duplicate key: a key appears more than once and needs review.

Does SheetDiff compare separate spreadsheet files?

Not yet. Version 9 compares two tabs inside the same Google spreadsheet. Cross-file comparison is planned; for now, copy or import the two datasets into separate tabs in one spreadsheet before comparing them.

Is SheetDiff free?

Yes. The free tier includes 5 comparisons per day with up to 500 data rows per comparison. Pro is a $29 one-time purchase for larger comparisons and advanced review, export, highlighting, preset, duplicate, and merge workflows.

Where is spreadsheet data processed?

Comparison work runs inside Google Sheets through Apps Script. SheetDiff does not require a separate account or an upload to a SheetDiff server. Pro activation makes an external request to verify the license. Read the privacy policy for the complete data-handling details.