To remove duplicates in Excel, click anywhere in your data, go to the Data tab and click Remove Duplicates in the Data Tools group. Tick the columns Excel should compare (all columns removes only identical rows; one column removes repeats of that value), make sure My data has headers is ticked, and click OK. Excel deletes the duplicate rows, keeps the first occurrence of each, and tells you how many were removed.
Duplicate rows creep into every list: a customer entered twice, an export run two times, two lists pasted together. This guide covers four ways to deal with them, from the one-click Remove Duplicates button to formula methods that leave your original data untouched, plus what to check when Excel misses duplicates you can see.
How do you remove duplicates in Excel with Remove Duplicates?
- Click any cell in your data (or select the exact range).
- Go to Data > Data Tools > Remove Duplicates.
- Tick My data has headers if your first row contains headings.
- Choose the columns to compare:
◦ All columns ticked removes rows that are identical across every column.
◦ One column ticked (for example Email) removes every row whose email has already appeared, even if other columns differ. - Click OK. Excel reports how many duplicates were removed and how many unique values remain.
Remove Duplicates deletes rows permanently (press Ctrl + Z straight away to undo), so it is worth working on a copy of important data. If your data is in an Excel Table, the same button is on the Table Design tab.
How do you find and highlight duplicates without deleting them?
Sometimes you want to review duplicates before removing anything:
- Select the column or range to check.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a format and click OK.
Every repeated value is now highlighted. Use Data > Filter and Filter by Color to see only the duplicates.
To count duplicates instead, add a helper column with =COUNTIF($A$2:$A$500,A2)>1. It returns TRUE for every value that appears more than once.
How do you remove duplicates with a formula?
In Excel for Microsoft 365 and Excel 2021 or later, the UNIQUE function returns a duplicate-free list in a new location and updates automatically when the source data changes:
=UNIQUE(A2:A500)returns each value once.=UNIQUE(A2:C500)returns each unique row across three columns.=UNIQUE(A2:A500,,TRUE)returns only the values that appear exactly once.
Type the formula in an empty cell and the results spill down automatically. Your original list stays as it is, which makes UNIQUE ideal for reports and drop-down lists.
How do you extract unique records with Advanced Filter?
Advanced Filter works in every version of Excel and copies a de-duplicated list elsewhere, leaving the original intact:
- Click in your data and go to Data > Advanced (in the Sort & Filter group).
- Choose Copy to another location.
- Check the List range, then pick a cell for Copy to.
- Tick Unique records only and click OK.
For more on this tool, see our guide to advanced filters in Excel.
Why is Excel not removing all the duplicates?
If you can see duplicates that Remove Duplicates or UNIQUE missed, the values are probably not exactly the same:
- Extra spaces. “Smith” and “Smith ” look identical but are different. Clean them with =TRIM(A2) in a helper column first.
- Numbers stored as text. 1001 as a number and “1001” as text do not match. Convert them so the whole column has one type.
- Different columns ticked. If you ticked every column, rows only count as duplicates when all of them match.
- Hidden characters from exported systems. =CLEAN(A2) removes non-printing characters.
Note that Remove Duplicates is not case-sensitive: “SMITH” and “smith” count as the same value.
Which method should you use?
- Remove Duplicates: fastest for a one-off clean-up, but it deletes rows.
- Conditional Formatting: for checking and reviewing before you delete anything.
- UNIQUE: for a live, self-updating list that leaves the source alone (Microsoft 365 and Excel 2021+).
- Advanced Filter: for a static de-duplicated copy in any version of Excel.
If you clean the same export every week, Power Query (Data > Get & Transform) can remove duplicates automatically each time you refresh.
Practice on your own spreadsheet
Grab a mailing list or export you work with, highlight the duplicates first, then remove them on a copy. For more built-in tools like this, see our 7 most useful Excel functions for data analysis.
Data cleaning, functions like UNIQUE and COUNTIF, and Power Query are covered in our instructor-led Excel training courses, in-class in Sydney and Melbourne or Online Live across Australia. For the bigger picture, see our Complete Guide to Microsoft Excel.
Frequently asked questions
Click in your data, go to Data > Remove Duplicates, tick the columns to compare and click OK. Excel deletes the duplicate rows, keeps the first occurrence of each, and tells you how many it removed.
Select the range and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Repeated values are highlighted so you can review them, and you can filter by colour to see only the duplicates.
In the Remove Duplicates dialog box, untick all columns except the one to check, such as Email, and click OK. Excel removes every row whose value in that column has already appeared, keeping the first.
Use the UNIQUE function in Microsoft 365 or Excel 2021 and later, for example =UNIQUE(A2:A500). It returns each value once in a new location and updates automatically, leaving the original data unchanged.
The values are usually not identical. Extra spaces, numbers stored as text, or hidden characters make entries look the same but count as different. Clean them with TRIM or CLEAN first, then remove duplicates again.
It keeps the first occurrence, working from the top of the list down, and deletes the later ones. Sort your data first if you want a particular row, such as the most recent, to be the one that stays.
