How to remove duplicates in Excel (and why some duplicates survive)

Duplicate rows creep into every spreadsheet that collects data from more than one place: two CRM exports pasted together, a form that was submitted twice, a list that was appended instead of replaced. Excel has several ways to remove them, and each one answers a slightly different question.

Here are five methods, from one click to fully formula-driven, plus the reasons duplicates sometimes survive all of them.

Method 1: The Remove Duplicates button

This is the fastest option and works in every version since Excel 2007.

1. Click any cell inside your table.
2. Go to Data > Data Tools > Remove Duplicates.
3. Tick My data has headers if the first row holds column names.
4. Choose the columns to compare:
   • All columns removes rows that match in every column.
   • Only some columns (for example, just Email) removes rows that share that value, even if the other columns differ.
5. Click OK. Excel reports how many duplicates it removed and how many unique values remain.

Excel keeps the first occurrence and deletes the rest. If you want to keep the most recent record, sort the newest rows to the top first.

Choosing a subset of columns throws away data in the columns you did not tick. If two rows share an email but have different phone numbers, one phone number is lost. Check before you click OK.

Method 2: The UNIQUE function (Excel 365 and 2021)

UNIQUE returns a de-duplicated copy and leaves the original untouched, so nothing is lost if you got it wrong.

=UNIQUE(A2:D500)

The result spills into as many rows as it needs. Useful variations:

• =UNIQUE(A2:A500) lists each value in one column once.
• =UNIQUE(A2:A500, FALSE, TRUE) lists only the values that appear exactly once, which is handy for finding records that have no duplicate.
• =SORT(UNIQUE(A2:D500)) returns the unique rows sorted.

Because it is a formula, the output updates when the source changes. Copy it and use Paste Special > Values when you want a fixed list.

Method 3: Advanced Filter (any version)

Before UNIQUE existed, this was the way to extract unique rows without deleting anything.

1. Select the range, including headers.
2. Go to Data > Sort & Filter > Advanced.
3. Choose Copy to another location and pick an empty destination cell.
4. Tick Unique records only and click OK.

Method 4: Highlight duplicates first

When duplicates need a human decision (which of two customer records is correct?), highlight them instead of deleting them.

1. Select the column to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Pick a colour and click OK.

Then filter by colour to review the repeated rows together.

Method 5: A COUNTIF helper column

For full control, mark each row as the first copy or a repeat with a helper column. With emails in column B starting at row 2:

=IF(COUNTIF($B$2:B2, B2) > 1, "Duplicate", "First")

The range grows as the formula is filled down, so the first occurrence says First and every later one says Duplicate. Filter the helper column to Duplicate, delete the visible rows, then remove the filter.

To check several columns at once, join them in another helper column first, for example =B2&"|"&C2&"|"&D2, and run COUNTIF on that.

Why some duplicates are not removed

If Excel says “0 duplicate values found” but you can see repeats, the cells differ in a way you cannot see:

Cause	Example	Fix
Trailing or leading spaces	Acme vs Acme	=TRIM(A2) in a helper column
Non-breaking spaces from web pages	Acme with character 160	=SUBSTITUTE(A2, CHAR(160), " "), then TRIM
Numbers stored as text	1001 (text) vs 1001 (number)	Data > Text to Columns > Finish on the column
Same date, different formats	3/8/2026 vs Mar 8, 2026	Apply one date format to the whole column
Hidden line breaks	Acme⏎	=CLEAN(A2)

Excel’s Remove Duplicates compares values as they are displayed, which is why the date row above counts as two different values, and it is not case-sensitive, so ACME and acme count as one.

Remove duplicates without opening Excel

If the file is an export you just need to clean, our Excel Data Cleaner does the cleanup in one pass: it trims whitespace, standardizes dates to ISO format and removes blank rows before checking for duplicates, so rows that only differed by a stray space or a date format are caught. It then removes exact duplicate rows, keeps the first copy, and gives you a clean .xlsx back. It runs in your browser, so the workbook is never uploaded.

Excel Data CleanerStrip empty rows/columns, remove duplicates, normalize headers, convert dates, and clean up messy workbooks.Clean an Excel file →

For CSV exports, the CSV Cleaner does the same for plain-text files and shows how many duplicate and blank rows it removed.

If the duplicates are spread across two workbooks rather than inside one, compare the two Excel files on a key column such as an ID to see which records appear in both. Empty rows left behind by deletions are covered in how to remove blank rows in Excel.