To remove duplicates in Excel, select the data and click Data > Remove Duplicates, choose the columns that must match, and press OK: Excel deletes every repeated row and keeps the first one. To keep the original list and get a clean copy instead, type =UNIQUE(A2:A9) in an empty column.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Without duplicates | |
| 2 | Ana | Ana | |
| 3 | Ben | Ben | |
| 4 | Cara | Cara | |
| 5 | Ana | Dan | |
| 6 | Dan | Eve | |
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
The highlighted cells in column A (rows 5, 7 and 8) are the repeats: those are the rows Remove Duplicates would delete. Column C is the list without them: Ana, Ben, Cara, Dan, Eve. Change A6 to Ana and both the highlight and the list update; the button would need to be run again.
Remove duplicates with the Remove Duplicates button
- Click any cell inside the data. Excel selects the whole block around it; to work on part of a sheet, select that range yourself.
- Go to Data > Remove Duplicates (in the Data Tools group). On Windows the key sequence is Alt, A, M. Inside an Excel table, the same button is also on the Table Design tab.
- Leave My data has headers ticked if row 1 holds column names, so the header row is never compared.
- Tick the columns that must all match for two rows to count as duplicates. With every column ticked, only fully identical rows are removed.
- Press OK. Excel reports how many duplicate values it found and removed and how many unique values remain.
What the button does, and what to know before you press it:
- It deletes the duplicates in place and moves the cells below up. Only the selected columns move: cells outside the selection stay where they are, so select the full width of the table or its rows fall out of line. Press Ctrl+Z (Cmd+Z on a Mac) to undo straight away if the result is wrong.
- It keeps the first occurrence, from the top.
- Upper and lower case are ignored:
anaandAnaare duplicates. - It compares the values as they are displayed. The same date formatted as
3/15/2026in one cell and15-Mar-26in another counts as two different values.
The button gives you no record of what it removed. When that matters, mark the duplicates first with a formula, look at them, and then delete.
Find duplicates before you delete them
A flag column shows which rows are repeats. =COUNTIF($A$2:A2,A2)>1 counts the value of the current row in the rows from the top down to this one. The start of the range, $A$2, is locked and its end, A2, is not, so the range grows as the formula is filled down: TRUE means "this value already appeared above", exactly the rows Remove Duplicates deletes.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Repeat? | Times in list |
| 2 | Ana | FALSE | 3 |
| 3 | Ben | FALSE | 2 |
| 4 | Cara | FALSE | 1 |
| 5 | Ana | TRUE | 3 |
| 6 | Dan | FALSE | 1 |
| 7 | Ben | TRUE | 2 |
| 8 | Ana | TRUE | 3 |
| 9 | Eve | FALSE | 1 |
Click B5: its range is $A$2:A5, which holds Ana twice, so B5 is TRUE. Column C uses a fixed range, $A$2:$A$9, so it counts every occurrence: each Ana row shows 3, and Cara, Dan and Eve show 1. Use column C to find every copy of a duplicate, the first one included; use column B to find only the extra ones.
To delete the flagged rows by hand, turn on Data > Filter, filter column B to TRUE, select the visible rows, right-click and choose Delete Row, then clear the filter. To colour the duplicates instead of flagging them, see [highlight duplicates](/docs/excel/highlight-duplicates).
Remove duplicates based on two columns
Two rows are duplicates only when both Name and City repeat. In the Remove Duplicates dialog, tick both columns. In a formula, give UNIQUE both columns: =UNIQUE(A2:B8) compares whole rows. The flag column in C does the same with COUNTIFS, which counts rows where both conditions match.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | City | Repeat? | Unique rows | |
| 2 | Ana | Lima | FALSE | ||
| 3 | Ben | Oslo | FALSE | ||
| 4 | Ana | Rome | FALSE | ||
| 5 | Ben | Oslo | TRUE | ||
| 6 | Cara | Lima | FALSE | ||
| 7 | Ana | Lima | TRUE | ||
| 8 | Dan | Rome | FALSE |
Your turn: List each Name and City pair once in E2.
Ana appears three times but only one of her rows is a duplicate: Ana in Rome is a different row from Ana in Lima. C5 and C7 are TRUE. The answer spills two columns wide, so it needs E and F empty.
Remove duplicates based on one column and keep the other columns
When only one column decides (one row per customer, whatever the city), tick only that column in the dialog. Excel keeps the first row for each name with all its columns. The formula version keeps each row whose name appears there for the first time: MATCH(A2:A8,A2:A8,0) gives the position of each name's first appearance, and that equals the row's own position only on first appearances.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | City | Name | City | |
| 2 | Ana | Lima | Ana | Lima | |
| 3 | Ben | Oslo | Ben | Oslo | |
| 4 | Ana | Rome | Cara | Lima | |
| 5 | Ben | Oslo | Dan | Rome | |
| 6 | Cara | Lima | |||
| 7 | Ana | Lima | |||
| 8 | Dan | Rome |
The result is Ana in Lima, Ben in Oslo, Cara in Lima and Dan in Rome. Ana's later cities (Rome, Lima) are dropped, just as the button drops them.
A sorted list, and values that appear only once
Wrap UNIQUE in SORT to get the clean list in alphabetical order. UNIQUE's third argument, exactly_once, does something different: with TRUE it returns only the values that never repeat, so every value that has a duplicate disappears completely, the first copy included. Remove Duplicates has no option for that.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Sorted | Only once | ||
| 2 | Eve | Ana | Cara | ||
| 3 | Ben | Ben | Dan | ||
| 4 | Cara | Cara | |||
| 5 | Ana | Dan | |||
| 6 | Dan | Eve | |||
| 7 | Ben | ||||
| 8 | Ana | ||||
| 9 | Eve |
C2 spills Ana, Ben, Cara, Dan, Eve. E2 returns only Cara and Dan: Eve, Ben and Ana each appear twice. For descending order use =SORT(UNIQUE(A2:A9),,-1). More on the function itself, including by_col, is on the UNIQUE page.
Count how many duplicates there are
The number of rows Remove Duplicates would delete is the number of rows minus the number of unique values. ROWS(A2:A9) counts the rows and COUNTA(UNIQUE(A2:A9)) counts the distinct customers. To count the distinct values on their own, see count unique values.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Repeats | |
| 2 | Ana | ||
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Ana | ||
| 6 | Dan | ||
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
Your turn: In C2, count how many rows of A2:A9 are repeats of a row above.
The answer is 3. If you already have the flag column from the section above, =COUNTIF(B2:B9,TRUE) counts the same repeats.
Remove duplicates in Excel 2019 and older
UNIQUE, SORT and FILTER need Excel 2021 or Microsoft 365. In Excel 2019 and earlier, there are two ways that keep the original:
- Advanced Filter. Select the column, go to Data > Sort & Filter > Advanced, choose Copy to another location, pick a target cell in Copy to, tick Unique records only, and press OK. The copy is a fixed list; run the filter again after the data changes.
- A formula filled down. Put this in C2, confirm it with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) because it is an array formula in older Excel, and fill it down until it shows blanks:
=IFERROR(INDEX($A$2:$A$9,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$9),0)),"")
Each row looks for the first value of A2:A9 that is not yet in the list above it. Google Sheets has =UNIQUE(A2:A9) too, but it is case-sensitive there: ana and Ana both stay.
Why Remove Duplicates misses some duplicates
Two cells that look identical are not duplicates when one has a space at the end. Remove Duplicates and UNIQUE both compare the text character by character (ignoring only upper and lower case), so Ana and Ana with a trailing space stay two entries.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | UNIQUE | UNIQUE of TRIM | ||
| 2 | Ana | Ana | Ana | ||
| 3 | Ana | Ana | Ben | ||
| 4 | Ben | Ben | Cara | ||
| 5 | Ben | Ben | |||
| 6 | Cara | Cara |
C2 spills all five entries, because the spaces make the copies different. TRIM removes the spaces at both ends, so E2 returns Ana, Ben and Cara. Before you run the button on imported data, add a column with =TRIM(A2) filled down, paste it over the original as values (Home > Paste > Paste Values), and then remove duplicates. Data copied from web pages can also contain non-breaking spaces, which TRIM does not remove; =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) handles both.
Frequently Asked Questions
How do I remove duplicates in Excel?
Click a cell in the data, go to Data > Remove Duplicates, tick the columns that must all match for a row to count as a duplicate, and press OK. Excel keeps the first occurrence of each row and deletes the others.
How do I remove duplicates in Excel without deleting the original data?
Use a formula in an empty column: =UNIQUE(A2:A9) lists every value once and leaves A2:A9 as it is. UNIQUE needs Excel 2021 or Microsoft 365; in older versions use Data > Sort & Filter > Advanced with Unique records only and Copy to another location.
Which duplicate does Remove Duplicates keep?
The first one, counted from the top of the selection. To keep the last occurrence instead, sort the data so the rows you want to keep come first, then run Remove Duplicates.
Why does Remove Duplicates not remove all duplicates?
Because the values are not exactly the same: a trailing space (Ana vs Ana), a non-breaking space, or the same date shown in two different formats makes two cells look equal but differ. Clean them first with a helper column such as =TRIM(A2). Upper and lower case do not matter: ana and Ana count as duplicates.
How do I count duplicates in Excel?
=COUNTIF($A$2:$A$9,A2) gives how many times the value in A2 appears. The number of rows Remove Duplicates would delete is =ROWS(A2:A9)-COUNTA(UNIQUE(A2:A9)).
