How to Find Duplicate Values in Two Columns in Excel

Method 1: COUNTIF Formula

If column A contains the first list and column B contains the second list, use this formula next to A2:

=COUNTIF($B:$B,A2)>0

TRUE means the value from column A exists somewhere in column B. FALSE means it is missing from column B.

Method 2: Conditional Formatting

1. Select both columns.
2. Go to Home > Conditional Formatting.
3. Choose Highlight Cells Rules > Duplicate Values.
4. Pick a highlight color and click OK.

This is quick, but it highlights duplicates anywhere in the selected range. Use formulas if you need more control.

Method 3: Compare Normalized Values

Duplicates are often hidden by spaces or capitalization. Normalize values before comparing:

=COUNTIF($B:$B,TRIM(A2))>0

For stricter cleanup, create helper columns with LOWER(TRIM(A2)) and compare those helper values.

Method 4: Use a Duplicate Finder Tool

The Duplicate Row Finder is better when duplicates are defined by multiple columns, such as email plus phone, customer ID plus region, or invoice number plus date.

Before You Delete Anything

Duplicates are not always wrong. A customer can place multiple orders. A product can appear in multiple regions. Before deleting rows, decide whether you are looking for duplicate values, duplicate records, or repeated valid events.