Guide
Find Duplicates in Two Columns in Excel
Two lists, one question: what appears in both? Whether it’s emails against an unsubscribe list or this month’s orders against last month’s, here is how to find duplicates across two columns — highlighted, listed, or counted.
1. Highlight them: conditional formatting
Select both columns, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel colors every value that appears more than once across the selection. Fast and visual — but it can’t tell you which column the duplicate came from, and you can’t filter on a color rule directly.
2. List them: COUNTIF
Next to column A, add =COUNTIF(B:B,A2)>0 and fill down. TRUE means the value
in A also exists in B. Now you can filter the TRUE rows into a clean list of
duplicates, or flip the test to =COUNTIF(B:B,A2)=0 to get values unique to A.
3. Pull the matching row: VLOOKUP
When you need more than “is it there?” — say, comparing the amount attached to the same
order ID in both lists — use =VLOOKUP(A2,B:C,2,FALSE) to bring the matching
value over, then compare the two columns directly.
Doing this in the browser
The same formulas work in TableDI, an online data
table with a formula panel that includes COUNTIF and VLOOKUP — load
two sheets, flag the overlaps, and filter the result without desktop Excel. Formula usage is
covered in the TableDI docs.
FAQ
How do I find duplicates between two columns in Excel?
Select both columns, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to see them instantly. For a filterable list, add =COUNTIF(B:B,A2)>0 next to column A — TRUE marks values that also appear in column B.
How do I find values in column A that are NOT in column B?
Use =COUNTIF(B:B,A2)=0. Rows showing TRUE exist only in column A. This is the standard way to diff two lists — for example, which subscribers are missing from an export.
Can I find duplicates across two different Excel files?
Yes — point COUNTIF at the other workbook, e.g. =COUNTIF([Book2]Sheet1!A:A,A2)>0, or load both sheets into an online data table like TableDI and run the same formula in the browser.