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?

Yes. In the SheetDiff sidebar, choose Compare with another file and paste the other spreadsheet's link. It is added as a tab with IMPORTRANGE (Google asks you once to click Allow access). You can also upload a CSV or Excel file there. See comparing two Google Sheets files.

Is SheetDiff free?

Yes. The free tier compares sheets of any size, 5 times a day, and lists the first 25 rows of each type of difference. Pro is $10 a year for complete lists plus export, highlighting, presets, 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.