How to remove duplicates in Excel
To remove duplicates in Excel, select your table, open the Data tab and choose Remove Duplicates. Excel keeps the first row of each match and deletes the rest. This guide shows that method, a safer way that leaves your data untouched, and how to spot duplicates before you delete anything.
You can practice each step in a live grid in the lessons linked at the end, so a mistake costs nothing.
1. Remove duplicate rows with the button
Click any cell in your table, then go to Data > Remove Duplicates (on Windows the keys are Alt, A, M). Tick "My data has headers" if the first row holds column names. Then choose the columns that must match. Tick all of them to remove only rows that are identical from end to end, or tick one column, such as an ID, to remove every row that repeats that ID.
Excel keeps the first row it finds, deletes the later ones, and tells you how many it removed. The rows are deleted from the sheet, so copy the data to a new sheet first if you might need the original. The comparison ignores upper and lower case.
2. List each value once without deleting anything
In Microsoft 365 and Excel 2021 or later, UNIQUE returns each value once and leaves the source data as it is. The result spills into the cells below the formula, so those cells must be empty. Wrap it in SORT for an A to Z list.
Example: Each name once
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Without duplicates | ||
| 2 | Ana | Ana | ||
| 3 | Ben | Ben | ||
| 4 | Ana | Cam | ||
| 5 | Cam | Dee | ||
| 6 | Ben | |||
| 7 | Dee |
Six names become four. Ana and Ben appear twice in the source and once in the list.
3. Find duplicates before you delete them
Select the column, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every repeated value gets a color, so you can check whether the repeats are real mistakes. A customer who bought twice is a repeat that you do not want to delete. To count how many times each value appears, use COUNTIF.
4. Clean the data first
"Ana" and "Ana " with a trailing space count as different values, so neither method removes the repeat. Run TRIM over the column first and paste the result back as values, then remove the duplicates.
Common mistakes
Removing duplicates from only one column of a table. The other columns can then end up on the wrong rows.
Fix: Select the whole table before you open Remove Duplicates, and tick only the columns that define a repeat.
Deleting without a copy. Remove Duplicates cannot be undone after you save and close the file.
Fix: Work on a copy of the sheet, or use UNIQUE so the source stays intact.
Missing repeats because of spaces or spelling. Extra spaces make two matching values look different to Excel.
Fix: Clean the column with TRIM first.
Frequently asked questions
Does Excel keep the first or the last duplicate?
The first. Remove Duplicates keeps the first row it finds for each match and deletes the later ones. Sort the data first if you want a different row to survive.
Is Remove Duplicates case sensitive?
No. "ana" and "ANA" count as the same value.
How do I remove duplicates in an older version of Excel?
The Remove Duplicates button works in older versions too. UNIQUE needs Microsoft 365 or Excel 2021 or later.
Related functions
- UNIQUEUNIQUE returns each value of a list once, removing duplicates with a formula that updates by itself.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
- SORTSORT returns a sorted copy of a range that updates when the data changes, ascending or descending, by any column.
More on removing duplicates
The guide shows the button, UNIQUE and highlighting. This piece adds how to choose which row survives, how to flag repeats with a helper column before you delete anything, and how to treat rows that only repeat across several columns.
Decide which row survives
Remove Duplicates keeps the first row it meets and deletes the later ones, so the order of your data decides the result. If you want the most recent entry for each customer, sort the table by date from newest to oldest first. Then the newest row comes first and is the one that stays.
Sorting first is a small step that avoids the most common surprise: finding out afterwards that the old address was kept and the new one was deleted.
Flag the repeats with a helper column
A helper column lets you look at the repeats before they disappear. Next to the data, count how many times the value has appeared so far, from the first row down to the current one. The first occurrence gives 1, the second gives 2, and so on.
=COUNTIF($A$2:A2,A2)>1Filter the helper column for TRUE and read the rows. If they are all real mistakes, delete them. If some are legitimate, such as a second order from the same customer, you have saved yourself from an error. This works with COUNTIF in every version of Excel.
Rows that repeat across several columns
Sometimes a row is a duplicate only when the name, the date and the amount all match. In the Remove Duplicates window, tick all three columns and Excel treats two rows as the same only if every ticked column is equal. To flag them with a formula, join the columns into one key and count that key.
=A2&"|"&B2&"|"&C2
=COUNTIF($E$2:E2,E2)>1The bar between the parts stops two different rows from producing the same key by accident. After cleaning, compare the row count with your expectation. For the other approach that leaves the source untouched, read [the UNIQUE function](/functions/unique), and to count what is left, see [how to count unique values](/guides/count-unique-values-in-excel).