How to highlight duplicate rows across two sheets

1Highlight values that also appear on the other sheet

1. On Sheet1, select A2:A500 (start from A2 so A2 is the active cell).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the formula below, click Format, pick a fill color on the Fill tab, and click OK twice.

=COUNTIF(Sheet2!$A$2:$A$500,$A2)>0

COUNTIF counts how many times the value in A2 appears on Sheet2. Anything above 0 is a duplicate. Excel applies the rule to each selected row in turn, so $A2 becomes $A3, $A4 and so on.

Referring to another sheet in a conditional formatting rule works in Excel 2010 and later. To highlight the matches on Sheet2 too, create the same rule there pointing back at Sheet1.

2Highlight the whole row

Select A2:C500 instead of one column and use the same formula. The $ before A keeps every cell in the row checking column A, so the whole row lights up.

=COUNTIF(Sheet2!$A$2:$A$500,$A2)>0

3Match on several columns with COUNTIFS

A row is only a true duplicate if several columns match, for example name, date and amount. COUNTIFS checks them all at once:

=COUNTIFS(Sheet2!$A$2:$A$500,$A2,Sheet2!$B$2:$B$500,$B2,Sheet2!$C$2:$C$500,$C2)>0

Use it as the conditional formatting formula with A2:C500 selected.

4Match whole rows with a helper key

For many columns, join each row into one text key. Add it in an empty column (here D) on both sheets:

=A2&"|"&B2&"|"&C2

In Excel 2019 and later, TEXTJOIN is shorter for long rows:

=TEXTJOIN("|",FALSE,A2:C2)

Then highlight with a simple COUNTIF on the key column:

=COUNTIF(Sheet2!$D$2:$D$500,$D2)>0

The | separator stops different rows from producing the same key, such as "ab" + "c" and "a" + "bc". Dates are joined as serial numbers, which is fine as long as both sheets hold real dates. You can hide the key column afterwards.

5Find values in one list that aren't in the other

Flip the test to =0 to highlight rows on Sheet1 that are missing from Sheet2:

=COUNTIF(Sheet2!$A$2:$A$500,$A2)=0

To label them in a column instead, put this in an empty column and fill it down:

=IF(COUNTIF(Sheet2!$A$2:$A$500,A2)=0,"Only on Sheet1","On both")

In Excel 2021 and Microsoft 365, FILTER lists them all in one go:

=FILTER(A2:A500,(A2:A500<>"")*(COUNTIF(Sheet2!A2:A500,A2:A500)=0))