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 Tablize, 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.
If the two columns live in two separate files and you want them pooled into one table before de-duplicating, merge Excel files into one stacks them in your browser first — then Data → Remove Duplicates finishes the job.
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 and run the same formula in the browser.