In an empty column of Sheet1, enter =COUNTIF(Sheet2!A:A, A2)>0 and fill it down.
TRUE means the value in A2 also appears in column A of Sheet2, in any row. To color
duplicates instead, use the same test in conditional formatting.
Suppose both tabs have an email in column A, with headers in row 1. In an empty column of
Sheet1, enter this in row 2 and fill it down:
=IF(COUNTIF(Sheet2!A:A, A2)>0, "In both", "")
To list every duplicate in one place, put this in an empty cell instead:
=FILTER(A2:A, A2:A<>"", COUNTIF(Sheet2!A2:A, A2:A)>0)
Wrap it in UNIQUE() to list each value once. If nothing matches, FILTER shows #N/A.
Change >0 to =0 to list values missing from the other tab instead; see
how to compare two columns in Google Sheets. Tab names
with spaces need single quotes: 'Sheet 2'!A:A.
Conditional formatting cannot refer to another tab directly, so wrap the reference in INDIRECT:
Sheet1, select A2:A.=COUNTIF(INDIRECT("Sheet2!A:A"), A2)>0
The formula is written for the first cell of the range, and Sheets shifts it down row by row. A range starting
at A2 needs A2 in the formula. If you apply it to A1:A, use A1, though
the header then lights up when both tabs share it. Get this off by one and every highlight lands on the wrong
row.
To highlight duplicates in both sheets, add the mirror rule in Sheet2 with
INDIRECT("Sheet1!A:A"). To color whole rows, apply the rule to A2:D and use
$A2. With spaces in the tab name, write INDIRECT("'Sheet 2'!A:A").
A match on one column tells you the same email is in both tabs, not that the whole record is. To find rows where every column matches, turn each row into one key:
=TEXTJOIN("|", FALSE, A2:D2)Sheet1, flag rows whose key is also in Sheet2:=COUNTIF(Sheet2!E:E, E2)>0
FALSE keeps empty cells in the key, and the | separator stops "ab" + "c" from
matching "a" + "bc". Both tabs need the same column order. To skip the helper columns, use one formula in an
empty column of Sheet1:
=ARRAYFORMULA(IF(A2:A="", "", COUNTIF(Sheet2!A2:A&"|"&Sheet2!B2:B&"|"&Sheet2!C2:C&"|"&Sheet2!D2:D, A2:A&"|"&B2:B&"|"&C2:C&"|"&D2:D)>0))
For repeats inside a single tab, =COUNTIF(A:A, A2)>1 marks every copy, and
=COUNTIF(A$2:A2, A2)>1 marks only the second and later ones. As a conditional formatting rule
on A2:A, use =COUNTIF($A:$A, $A2)>1; no INDIRECT is needed within one tab.
To remove them, use Data → Data cleanup → Remove duplicates. It deletes rows in
place, so duplicate the tab first. =UNIQUE(A2:D) gives a deduplicated copy without touching the
original.
Formulas cannot refer to a tab in another file by name, so to compare spreadsheets for duplicates across
files, import the other list first. In a new tab called Other file, enter:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit", "Sheet1!A1:D")
Click Allow access once, then point the formulas above at 'Other file'!A:A. The
full walkthrough is in how to compare two Google Sheets
files.
=SUMPRODUCT(--(TRIM(Sheet2!A$2:A)=TRIM(A2)))>0.=SUMPRODUCT(--EXACT(Sheet2!A$2:A, A2))>0.ISTEXT(A2) shows which is which; comparing TO_TEXT() of both sides fixes it.* and ? as wildcards, so "A?" also matches "AB". If your data contains them, use the SUMPRODUCT formulas.Formulas suit one column. For whole rows across many columns, a monthly check, or a second list that is a CSV, Excel file or another Google Sheet, SheetDiff matches everything in one pass.
Results go to a new SheetDiff Report tab. Rows in both tabs with the same values are counted as Matching rows; the report gives the number, not a list. With a key column, a row whose key is in both tabs but whose other values differ is listed under Changed rows. Rows only in one tab are listed too.
Spaces are trimmed and case is ignored unless you tick Case sensitive comparison, and an ID stored as
text matches the same ID stored as a number. In whole-row mode, identical rows pair one for one: if a row
appears twice in Sheet1 and once in Sheet2, the extra copy is listed as only in
Sheet1.
The free plan compares tabs of any size, 5 times a day, with every count exact and the first 25 rows of each list. Pro is $10 a year. Comparing values rather than hunting duplicates? See how to compare two Google Sheets tabs.
With Pro, Highlight Sources adds a SheetDiff Status column at the end of each compared tab, writes MATCH, CHANGED or MISSING FROM A/B on each row, and tints rows green, yellow, or red and blue. Filter that column on MATCH to see every duplicate in both tabs. The tints are conditional formatting rules, and Clear Highlights removes them along with the status column.
If the key you match on appears more than once in a tab, SheetDiff warns instead of guessing which copy pairs with which. The sidebar shows the warning, and the report's Duplicate Key Warnings and Duplicate Review Queue sections list each repeated key, how often it occurs and its row numbers in each tab, in full on the free plan. Those rows stay out of the matching and changed counts. With Pro, Review Duplicates builds a sheet where you choose which rows pair up, then regenerates the report.
SheetDiff does not delete or merge duplicates; use Remove duplicates or UNIQUE for that.
No. COUNTIF searches the whole column, so a value is found wherever it sits in the other tab. SheetDiff's
key-column and whole-row matching work the same way. Only cell-by-cell checks such as
=A2=Sheet2!A2, or SheetDiff's row-order mode, depend on order.
No. SheetDiff counts and reports rows that appear in both tabs, flags repeated keys, and with Pro highlights matching rows. To delete repeated rows inside one tab, use Data → Data cleanup → Remove duplicates on a copy of the tab.
Running a comparison does not. Results go to a new SheetDiff Report tab. Pro highlighting is different: it adds a SheetDiff Status column and color rules to the compared tabs, and Clear Highlights removes both.