How To Remove Duplicates In Excel (2026)
Duplicate rows in an Excel list throw off totals, mailing lists and counts.
Excel has a built-in Remove Duplicates command on the Data tab that deletes repeat rows in a few clicks.
The steps below follow Microsoft’s support pages, including how to preview duplicates first and how to hide them without deleting anything.
Quick answer: remove duplicates in Excel
| What you want | Where to go |
|---|---|
| Delete duplicate rows | Data > Data Tools > Remove Duplicates |
| Highlight duplicates first | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values |
| Hide duplicates without deleting | Data > Sort & Filter > Advanced > Unique records only |
| Get a unique list by formula | =UNIQUE(range) |
| Undo a removal | Ctrl+Z |
Remove duplicate values step by step
- Select the range of cells, or click a cell inside your table.
- Go to the Data tab and select Remove Duplicates in the Data Tools group.
- Under Columns, tick the columns Excel should compare, or use Select All or Unselect All.
- Select OK.
- Read the message that tells you how many duplicates were removed and how many unique values remain, then select OK.
If the result is not what you expected, select Undo or press Ctrl+Z straight away.
Make a copy before you delete anything
Microsoft warns that removing duplicates permanently deletes data.
Its advice is to copy the original range or table to another worksheet or workbook first.
Only the range or table you selected is touched, so values outside it do not change or move.
How the column choice decides what is a duplicate
Excel matches rows on the combined values of the columns you tick.
When it finds a match, it removes the entire row, including the columns you left unticked.
| Columns ticked | A row counts as a duplicate when |
|---|---|
| Column1 and Column2 (Column3 unticked) | Both Column1 and Column2 match another row |
| All columns | Every value in the row matches another row |
| One column only | That single column matches another row |
That last row follows from Microsoft’s rule that the ticked columns are what get compared, so a single ticked column means that column alone decides.
Which copy Excel keeps
Microsoft says the first occurrence of a value stays and later identical entries are deleted.
If the order matters, sort the list the way you want before you run the command.
Why some duplicates are not removed
Excel compares what is displayed in a cell, not the value stored underneath.
| Cell shows | Stored value | Treated as |
|---|---|---|
| 3/8/2006 | The same date | Unique, because the display differs |
| Mar 8, 2006 | The same date | Unique, because the display differs |
This is Microsoft’s own example, and it means you should format matching dates and numbers the same way before removing duplicates.
Highlight duplicates first with conditional formatting
Microsoft recommends previewing duplicates before you remove them.
- Select the cells you want to check.
- On the Home tab, open Conditional Formatting in the Styles group.
- Select Highlight Cells Rules, then Duplicate Values.
- Pick the values and the format you want, then confirm.
For more control, choose Manage Rules, select New Rule, pick Format only unique or duplicate values, and set the Format all list to unique or duplicate.
Hide duplicates without deleting them
An Advanced Filter hides repeat rows temporarily and leaves your data intact.
- Select the range, or click a cell in your table.
- Go to Data > Advanced in the Sort & Filter group.
- Choose Filter the list, in-place, or Copy to another location and enter a cell in the Copy to box.
- Tick Unique records only and select OK.
The Copy to another location option gives you a clean unique list beside the original.
Use the UNIQUE function for a live unique list
On versions that support it, a formula can list unique values without touching the source data.
| Argument | What it does |
|---|---|
| array | The range to pull unique rows or columns from (required) |
| by_col | TRUE compares columns, FALSE or omitted compares rows |
| exactly_once | TRUE returns only values that appear once, FALSE or omitted returns every distinct value |
The syntax is =UNIQUE(array,[by_col],[exactly_once]).
Microsoft lists it for Excel for Microsoft 365, Excel 2024 and Excel 2021, so older versions need the Remove Duplicates command instead.
Wrap it in SORT, as in =SORT(UNIQUE(B2:B12&” “&A2:A12)), to return the results in order.
Which method to use
| Method | Deletes data | Best when |
|---|---|---|
| Remove Duplicates | Yes, permanently | You want the repeat rows gone and have a backup copy |
| Conditional Formatting | No | You want to see the duplicates before deciding |
| Advanced Filter, Unique records only | No, it hides them or copies unique rows elsewhere | You need a clean list but must keep the original |
| UNIQUE function | No | You use a version that lists it and want a list that updates |
When in doubt, start with the highlight, then filter, and only delete once you have confirmed the rows are true repeats.
If Remove Duplicates will not work
Microsoft says you cannot remove duplicates from outlined data or data with subtotals.
Remove the outline and the subtotals first, then run the command again.
Microsoft also notes that you cannot conditionally format fields in the Values area of a PivotTable by unique or duplicate values.
Tell half-width and full-width characters apart
Microsoft notes that if your authoring language uses double-byte character sets such as Japanese, half-width and full-width characters count as the same.
To tell them apart, use a conditional formatting rule built on COUNTIF, such as =COUNTIF($A$2:$A$400,A2)>1 for the range A2:A400.
Most English-language workbooks never need this.
Why the removal message can look wrong
Microsoft says the counts of duplicate and unique values that Excel reports may include empty cells and spaces.
So a number that looks too high or too low is not always a sign that the wrong rows were deleted.
Check the list after the command runs, and use Ctrl+Z if a row you wanted is gone.
Which Excel versions this covers
| Version | Remove Duplicates | UNIQUE function |
|---|---|---|
| Excel for Microsoft 365 | Listed by Microsoft | Listed by Microsoft |
| Excel 2024 | Listed by Microsoft | Listed by Microsoft |
| Excel 2021 | Listed by Microsoft | Listed by Microsoft |
| Excel 2019 and 2016 | Listed by Microsoft | Not listed |
Related Excel guides
If you removed rows by mistake and saved, see how to recover an unsaved Excel workbook or how to restore a previous version of a file from Version History.
To keep your column labels in view while you scan a long list, see how to freeze rows and columns in Excel.
If your formulas stop updating after you clean the list, read fixing Excel formulas that are not calculating.
Sources
Microsoft Support: Filter for unique values or remove duplicate values.
Microsoft Support: Find and remove duplicates.
Microsoft Support: UNIQUE function.