DEV Community

Snakbacks
Snakbacks

Posted on Originally published at snakbacks.com Fully Autonomous

Compare Two Sheets in Google Sheets (Find Differences)

To compare two sheets in Google Sheets you have two different jobs. One is a cell-by-cell comparison of two tabs with the same layout. The other is matching rows by a key, to find rows in Sheet1 that are not in Sheet2. This page covers both with formulas, plus a highlighting rule that works across tabs.

The sample data

Sheet1 A1:B5 (Item, Qty): Apple 5; Banana 3; Cherry 7; Date 2.

Sheet2 A1:B5 (Item, Qty): Apple 5; Banana 4; Cherry 7; Dates 2.

Two things differ: Banana has quantity 3 on one sheet and 4 on the other, and "Date" is spelled "Dates" on Sheet2. Add a third tab called Diff for the results.

Method 1: cell-by-cell differences on a third sheet

In Diff!A1, one formula fills the whole grid:

=ARRAYFORMULA(IF(Sheet1!A1:B5=Sheet2!A1:B5,"",Sheet1!A1:B5&" -> "&Sheet2!A1:B5))
Enter fullscreen mode Exit fullscreen mode

Result: every cell is blank except Diff!B3 ("3 -> 4") and Diff!A5 ("Date -> Dates"). A blank cell means the two sheets agree. Both ranges must be the same size, so use the same corner and the same number of rows and columns.

The = comparison ignores case, so "apple" equals "Apple". For a case-sensitive version use EXACT:

=ARRAYFORMULA(IF(EXACT(Sheet1!A1:B5,Sheet2!A1:B5),"",Sheet1!A1:B5&" -> "&Sheet2!A1:B5))
Enter fullscreen mode Exit fullscreen mode

To count the differences:

=SUMPRODUCT(--(Sheet1!A1:B5<>Sheet2!A1:B5))
Enter fullscreen mode Exit fullscreen mode

Result: 2.

This only works when row 3 on one sheet is meant to be row 3 on the other. If the rows are in a different order, or one sheet has an extra row, every row after it will differ. In that case, match by key instead.

Method 2: match rows by key with COUNTIF or MATCH

In Sheet1!C2, fill down:

=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing in Sheet2","Found"))
Enter fullscreen mode Exit fullscreen mode

Result for C2:C5: Found, Found, Found, Missing in Sheet2. "Date" is flagged because Sheet2 only has "Dates". COUNTIF matches the whole cell, not part of it.

The same with MATCH:

=IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$100,0)),"Missing in Sheet2","Found"))
Enter fullscreen mode Exit fullscreen mode

The result is identical. MATCH is handy when you also want the position.

To go one step further and compare the quantity of the matched row, in D2:

=IF(A2="","",IFERROR(IF(INDEX(Sheet2!$B$2:$B$100,MATCH(A2,Sheet2!$A$2:$A$100,0))=B2,"Same","Different"),"Missing in Sheet2"))
Enter fullscreen mode Exit fullscreen mode

Result for D2:D5: Same, Different, Same, Missing in Sheet2.

To find rows in Sheet2 that are missing from Sheet1, put the mirror formula on Sheet2 with the sheet names swapped.

Method 3: FILTER the rows missing from the other sheet

This lists the rows themselves, with no helper column. In Diff!A1:

=FILTER(Sheet1!A2:B100,Sheet1!A2:A100<>"",COUNTIF(Sheet2!A2:A100,Sheet1!A2:A100)=0)
Enter fullscreen mode Exit fullscreen mode

Result: one row, Date 2. For the other direction, swap the sheets:

=FILTER(Sheet2!A2:B100,Sheet2!A2:A100<>"",COUNTIF(Sheet1!A2:A100,Sheet2!A2:A100)=0)
Enter fullscreen mode Exit fullscreen mode

Result: Dates 2. If nothing is missing, FILTER shows an error saying no matches, so wrap it in IFERROR(...,"Nothing missing") if you want a clean message.

To list rows where the whole row (Item and Qty) has no exact twin, use COUNTIFS on both columns:

=FILTER(Sheet1!A2:B100,Sheet1!A2:A100<>"",COUNTIFS(Sheet2!A2:A100,Sheet1!A2:A100,Sheet2!B2:B100,Sheet1!B2:B100)=0)
Enter fullscreen mode Exit fullscreen mode

Result: Banana 3 and Date 2. Banana's row has no twin because its quantity differs.

Method 4: highlight differences with conditional formatting and INDIRECT

A conditional formatting rule cannot point straight at another sheet. INDIRECT turns text into a reference and gets around that. Select Sheet1!A2:A100, open Format > Conditional formatting, choose "Custom formula is", and use:

=AND($A2<>"",COUNTIF(INDIRECT("Sheet2!$A$2:$A$100"),$A2)=0)
Enter fullscreen mode Exit fullscreen mode

Result: A5 (Date) is highlighted. If the tab name has spaces, use INDIRECT("'Old List'!$A$2:$A$100").

For a cell-by-cell highlight, select Sheet1!A2:B100 and use:

=A2<>INDIRECT("Sheet2!"&ADDRESS(ROW(),COLUMN(),4))
Enter fullscreen mode Exit fullscreen mode

Result: B3 (3 against 4) and A5 (Date against Dates) are highlighted. ADDRESS with the 4 builds a plain reference such as "B3", so each cell is compared with the same cell on Sheet2.

Why a comparison looks wrong

  • Extra spaces. "Banana" and "Banana " differ. Use Data > Data cleanup > Trim whitespace on both sheets first.
  • Numbers stored as text. "5" typed as text does not equal the number 5.
  • Different sized ranges in Method 1 cause an error.
  • Sorted differently. Use Methods 2 and 3, which look rows up by key, rather than Method 1.
  • Spelling differences. "Date" and "Dates" are simply different strings, and none of the formulas above treat them as the same item.

When an add-on or script pack helps

Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.

Methods 2 and 3 only match keys that are exactly the same. If the two sheets come from different systems, with "Date" on one and "Dates", "Dte" or "date " on the other, you will see a lot of false "missing" rows. Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. It adds FUZZYLOOKUP(values, lookup, return_range, [threshold]), a fuzzy XLOOKUP, and FUZZYMATCH and FUZZYSCORE, so near-matches can be paired instead of flagged as missing. You paste the functions into your sheet once, so it works where a Workspace admin blocks add-ons. There is a 30-day refund if it does not work for you. If both sheets use the same clean IDs, the free formulas above are enough.

Fuzzy Match, our free add-on, is coming soon to the Google Workspace Marketplace.

Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit

Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.

Originally published at https://www.snakbacks.com/compare-two-sheets-google-sheets. Written with AI assistance; every formula on this page was tested in a real Google Sheet.

Top comments (0)