How to Remove Duplicates in Excel
Excel gives you three good ways to handle duplicates: the built-in Remove Duplicates button, the UNIQUE function (Excel 365), and Conditional Formatting to see duplicates before removing them. Each fits a different situation.
⚡ Quick Answer
Select your data, click Data → Remove Duplicates, choose which columns to check, and click OK. To keep the original data intact, use =UNIQUE(A1:A100) in a new location.
Method 1: Remove Duplicates button
Best for permanent removal from the source data.
Click inside your data
Click any cell in your dataset. Excel auto-detects the range. Or select the specific range yourself.
Open Data → Remove Duplicates
Go to the Data tab. In the Data Tools group, click Remove Duplicates.
Choose columns to check
In the dialog, tick the columns Excel should compare. Rows are considered duplicate only when all ticked columns match. Tick just "Email" to dedupe on email address; tick all columns to remove only exact-match rows.
Confirm
Click OK. Excel removes duplicates and shows a message: "N duplicate values found and removed; M unique values remain."
Remove Duplicates permanently deletes rows. If you want to preserve the original, copy the data to a new sheet first or use the UNIQUE function (Method 2).
Method 2: UNIQUE function (non-destructive)
Requires Excel 365 or Excel 2021+. Returns unique values in a new location without touching the source.
Click an empty cell
Choose a location where you want the unique list to appear.
Type the UNIQUE formula
Enter
=UNIQUE(A2:A1000)whereA2:A1000is your source range.Press Enter
Excel spills unique values down the column. As source data changes, the unique list updates automatically.
Pass a multi-column range: =UNIQUE(A2:C1000) returns unique rows (combinations of columns A, B, C). To get unique values regardless of column order, wrap in SORT: =SORT(UNIQUE(A2:A1000)).
Method 3: Highlight duplicates (see before you delete)
Best when you want to review duplicates before deciding what to do with them.
Select the range
Highlight the cells you want to check.
Open Conditional Formatting
Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
Choose formatting
Pick a color style (default is light red fill). Click OK. All duplicate values are highlighted so you can review and manually delete.
Comparison of methods
| Method | When to use | Modifies source? |
|---|---|---|
| Remove Duplicates | Permanent removal | Yes |
| UNIQUE function | Keep source intact, dynamic list | No |
| Conditional Formatting | Review before removing | No (visual only) |
| Advanced Filter | Complex criteria, copy to new location | No (if copied) |
Common gotchas
- Extra spaces trip up matching.
"apple"and"apple "look identical but Excel treats them as different. Clean with=TRIM(A1)first. - Numbers stored as text vs actual numbers.
"100"(text) and100(number) are different values. Convert with=VALUE(A1). - Case sensitivity varies. Remove Duplicates is case-insensitive; formula-based dedup (EXACT, MATCH) is typically case-sensitive.
- The "first occurrence" is kept. Excel deletes second-and-later duplicates. Sort your data first if you need a specific occurrence retained.
Keyboard shortcut
Remove Duplicates Shortcut
Excel Wizard — clean messy data faster
Remove Duplicates works when duplicates are exact. But real-world data has fuzzy duplicates — "John Smith" vs "john smith" vs "John Smith" (extra space). Excel Wizard's AI cleaning identifies fuzzy duplicates across text variations, standardizes formatting, and merges near-matches. Cell-level =AI() functions handle cleanup in one step.
Frequently asked questions
How do I remove duplicates in Excel without deleting them?
Use the UNIQUE function or Advanced Filter. UNIQUE (=UNIQUE(A1:A100)) returns unique values in a new location without modifying the original. Advanced Filter can copy unique values to a different location. Both leave your source data intact.
How do I highlight duplicates in Excel instead of removing them?
Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Choose a formatting style. Excel highlights every duplicate value so you can review them before deciding what to remove.
Why is Remove Duplicates not removing my duplicates?
Common reasons: (1) Extra spaces — 'apple' and 'apple ' are treated as different. Use TRIM to clean up. (2) Case differences — Excel's Remove Duplicates IS case-insensitive by default, but formulas comparing text may not be. (3) You unchecked columns in the dialog — only checked columns are compared. (4) Numbers stored as text vs actual numbers.
Can I remove duplicates based on one column only?
Yes. When the Remove Duplicates dialog appears, untick all columns except the one you want to check. Excel will remove rows where that one column has duplicates, keeping the first occurrence of each unique value.