How to Remove Duplicates in Excel

Removing duplicates is a thirty-second job that regularly goes wrong, because the tool does exactly what you tell it and people tell it the wrong thing. This guide covers the built-in tool, the choice that actually matters, how to review duplicates before deleting them, and the near-duplicates the tool cannot see.

The built-in tool, step by step

1. Click any cell inside your data. Excel selects the surrounding block automatically.
2. Data tab → Remove Duplicates.
3. Check "My data has headers" if the first row is column names — otherwise your header row can be treated as data.
4. Tick the columns that define a duplicate. This is the decision the whole operation hinges on; see the next section.
5. Click OK. Excel reports how many duplicates were removed and how many unique rows remain.

The first occurrence of each duplicate is kept, counting from the top. If which copy survives matters — say you want the most recent record — sort first so the row you want to keep is uppermost, then remove duplicates.

The column choice is the whole game

"Duplicate" means "identical in every ticked column". Different tickings give different answers, and both are sometimes right:

Columns ticked	What gets removed	Use when
All columns	Rows identical in every field	Cleaning accidental double-entries or a double paste
Email only	Every repeat of an email, even if the name or date differs	Building one-row-per-person lists
Name + Date	Same person on the same day	Deduplicating event logs

The common accident is ticking only one column when you meant "fully identical rows" — which silently deletes rows that merely share a value in that column. If the removal count is far higher than expected, that is almost certainly what happened. Ctrl+Z undoes it if you act before moving on.

See the duplicates before deleting them

Remove Duplicates deletes without showing you what it will delete. To review first, highlight them instead:

1. Select the column to check.
2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Every value that appears more than once lights up — including the first occurrence, which the removal tool would keep.

To count how many times each value appears, put this beside your data and fill down:

=COUNTIF(A:A, A2)

Anything above 1 is duplicated, and you can filter on that column to inspect the repeats — or flag only the second-and-later copies, which is exactly what removal would delete:

=COUNTIF(A$2:A2, A2) > 1

Keeping the original: two non-destructive routes

UNIQUE (Microsoft 365 and Excel 2021+): a formula that produces the deduplicated list next to the intact original.

=UNIQUE(A2:C500)

One formula, spills automatically, updates live when the source changes — which also makes it the right choice for data that keeps growing. In older Excel it returns #NAME?; use the Advanced Filter instead: Data → Advanced → Copy to another location → Unique records only, which achieves the same copy-not-delete result in any version.

The duplicates Excel cannot see

Remove Duplicates compares values exactly. Real-world data is rarely exact, and these all count as different values:

• Smith and Smith  — a trailing space makes them different values
• 1050 stored as a number and 1050 stored as text — different
• 29/08/2026 and 29/08/2026 09:30 displayed under a date format — different
• J. Smith and John Smith — different, and no tool fixes this automatically

The practical pre-clean before any deduplication of imported data:

=TRIM(CLEAN(A2))

TRIM strips leading, trailing and doubled spaces; CLEAN removes non-printing characters that ride in from web pages and PDFs. Run your key column through this into a helper column, paste back as values, then deduplicate. Skipping this step is the main reason "I removed duplicates and they are still there".

One case-sensitivity note worth knowing: because matching is case-insensitive, APPLE and apple are treated as duplicates and one will be removed. If case distinguishes your records, add a helper column with =EXACT() comparisons instead.

Numbers that look identical but are not

Two cells can display 3.14 and still not be duplicates, because one holds 3.14159 formatted to two decimals. Remove Duplicates compares the stored value, not the displayed one.

If displayed precision is what defines your duplicates, round first in a helper column — =ROUND(A2, 2) — and deduplicate on that. The same applies to dates carrying invisible time components: 29/08/2026 and 29/08/2026 09:30 are different values that display identically under a date format. Strip times with =INT(A2) before deduplicating.

Checklist before you click OK

• Copy the sheet. Right-click the tab → Move or Copy → Create a copy. Thirty seconds of insurance.
• TRIM/CLEAN the key columns if the data was imported or pasted.
• Sort so the survivor is on top, if which copy is kept matters.
• Highlight first with conditional formatting if you want to see what will go.
• Sanity-check the count. If Excel reports removing 4,000 duplicates from 5,000 rows and you expected a handful, undo and re-check which columns were ticked.

Microsoft's reference on unique and duplicate values covers the remaining edge cases.

If the workbook is also sluggish or unexpectedly large, why your Excel file is slow and large covers used range bloat and the volatile formulas that force constant recalculation.

Blank rows are the other common tidy-up, and the popular method for them is unsafe: how to remove blank rows in Excel explains why Go To Special deletes rows containing data, and what to use instead.

Frequently asked questions

How do I remove duplicates in Excel?

Select the data, go to Data → Remove Duplicates, tick the columns that define what makes a row a duplicate, and click OK. Excel keeps the first occurrence of each and deletes the rest. Copy the sheet first — the deletion is permanent once saved.

How do I remove duplicates without deleting the original rows?

Use =UNIQUE(range) in Microsoft 365 or Excel 2021+, which produces a deduplicated copy beside the untouched original and updates automatically. In older versions, Data → Advanced Filter with "Unique records only" and "Copy to another location" does the same.

Why does Excel say there are no duplicates when I can see them?

The values are not exactly equal — usually trailing spaces from imported data, or one value stored as text and the other as a number. Run the column through =TRIM(CLEAN(cell)), paste back as values, and try again.

Which duplicate does Excel keep?

The first occurrence from the top. If you need the newest record kept, sort by date descending before removing duplicates so the newest copy is the one that survives.

How do I highlight duplicates instead of removing them?

Select the column, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. To flag only the copies that removal would delete, use the formula =COUNTIF(A$2:A2,A2)>1 in a helper column.

Does Remove Duplicates treat "Apple" and "apple" as the same?

Yes — text matching is case-insensitive, so one of them will be removed. If case matters for your data, build a helper column using EXACT() comparisons and deduplicate on that instead.

Conclusion

The tool is simple; the judgement is in the column selection and the pre-clean. Copy the sheet, TRIM the keys, tick the columns deliberately, and check the removal count against your expectation before moving on.

Related: VLOOKUP vs XLOOKUP — duplicates in a lookup column are a classic source of wrong lookup results, since VLOOKUP silently returns only the first match — and converting units in Excel for the CONVERT function.