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.
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.
In Diff!A1, one formula fills the whole grid:
=ARRAYFORMULA(IF(Sheet1!A1:B5=Sheet2!A1:B5,"",Sheet1!A1:B5&" -> "&Sheet2!A1:B5))
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))
To count the differences:
=SUMPRODUCT(--(Sheet1!A1:B5<>Sheet2!A1:B5))
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.
In Sheet1!C2, fill down:
=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Missing in Sheet2","Found"))
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"))
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"))
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.
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)
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)
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)
Result: Banana 3 and Date 2. Banana's row has no twin because its quantity differs.
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)
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))
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.
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.
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.