How to Remove Duplicates in Excel (2026)
Excel has a built-in tool that strips duplicate rows in one click, plus ways to find duplicates before deleting and to build a clean unique list without touching the original. Here are all three.
1. Remove Duplicate Rows (Built-In Tool)
- Click any cell in your data (or select the range, including headers).
- On the Data tab, click Remove Duplicates.
- In the dialog, check My data has headers if it does.
- Tick the columns that define a duplicate, then click OK.
Excel deletes the extra rows in place and tells you how many it removed. This changes your data — see the backup tip below.
2. Dedupe by Specific Columns
A “duplicate” is defined by which columns you check. To remove rows with the same email even if other columns differ, check only the Email column in the dialog. To require a full-row match, check every column.
Excel keeps the first occurrence and removes the rest.
3. Find Duplicates First (Without Deleting)
To review before you delete, highlight them with conditional formatting:
- Select the range.
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Pick a color and click OK.
Now you can scan, edit, or filter the flagged rows. (More in conditional formatting.)
4. Build a Unique List With UNIQUE
In Excel 365/2021, the UNIQUE function returns the distinct values into a new location, leaving the source intact:
=UNIQUE(A2:A100)The result spills down automatically and updates as the source changes. Combine with SORT to order it:
=SORT(UNIQUE(A2:A100))5. Back Up Before You Delete
Remove Duplicates is permanent once you save. Before running it, copy the sheet (right-click the tab > Move or Copy > Create a copy) or work on a duplicate of the file. If you catch a mistake immediately, Ctrl+Z (Cmd+Z on Mac) undoes it.
6. Troubleshooting
Rows that look identical weren’t removed
There’s a hidden difference — usually a trailing space or text-vs-number. Clean the column with TRIM first, then dedupe.
It removed rows I wanted to keep
You checked too few columns, so Excel treated near-matches as duplicates. Undo, then check more columns to tighten the match.
Remove Duplicates is greyed out
Your cells are inside a feature that blocks it (like a subtotaled outline). Remove subtotals first, or copy the data to a fresh sheet.
Mac and Web
Data > Remove Duplicates works the same on Excel for Mac. On Excel for the web, use Data > Remove Duplicates as well, or the UNIQUE function.
Related Excel guides: How to remove blank rows · How to use conditional formatting · How to use VLOOKUP · How to create a drop-down list · How to use SUMIF
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
Get Carly Today →Or explore our free tools
