How to Remove Duplicates in Excel
Remove duplicate rows or values in Excel with Remove Duplicates, UNIQUE, or a review-first formula while keeping the right records.
Method 1: use Remove Duplicates
The built-in command is the fastest choice when the goal is to change the source list. Select one cell in a clean table or select the complete range, then go to Data > Remove Duplicates. Excel asks which columns should be compared. A row counts as a duplicate when it matches an earlier row in every selected column; the first occurrence is kept and the later rows are deleted.
The column choice changes the meaning of “duplicate.” For a customer export, selecting Email alone keeps one row per email. Selecting Email, Date, and Amount removes only rows that repeat all three fields. Include the whole table in the selection so the other columns move with their records. Excel ignores letter case and compares values as they are displayed, so the same date formatted two different ways is not seen as a duplicate. When the command finishes, read its message: it reports how many duplicate values were removed and how many unique values remain.
Method 2: create a unique list with a formula
If you want to preserve the original data, enter =UNIQUE(A2:A100) in an empty area. UNIQUE needs Microsoft 365, Excel 2021 or later, or Excel for the web. The result spills into neighboring cells and updates as the source changes. For several columns, use a rectangular range such as =UNIQUE(A2:C100); uniqueness is then judged on complete rows.
A spilled list needs clear cells below and to the right, or Excel shows #SPILL!. When rows are appended, convert the source to an Excel Table and refer to a column, for example =UNIQUE(tblCustomers[Email]), so the range expands with the table. UNIQUE suits a report or a validation list, but it never deletes anything from the source.
Method 3: mark duplicates before deleting
A review column is safer when you must decide which record to keep. With values in A2:A100, enter =COUNTIF($A$2:$A$100,A2)>1 and fill down. TRUE means the value occurs more than once. To mark only later occurrences, use =COUNTIF($A$2:A2,A2)>1. Filter the helper column to TRUE, inspect dates or status fields, and delete only approved rows.
Text that looks identical may differ by trailing spaces or nonprinting characters. A cleanup key such as =TRIM(CLEAN(A2)) can expose those cases before counting. Do not clean identifiers where spaces are meaningful.
Which row survives, and what to watch for
Remove Duplicates always keeps the first row it meets, so sort deliberately when the newest or highest-priority record must survive. Rows that are empty in every selected column can also be treated as duplicates of each other. Keep a backup before deleting, and press Ctrl+Z right away if the message reports a different count than you expected; the command cannot be undone after the workbook is saved and closed.
If the same cleanup repeats every week, Power Query is a better home for it: Home > Remove Rows > Remove Duplicates is a recorded step that runs again on refresh.
Your turn
In C2, count how many times the email in A2 appears in A2:A6.
| A | B | C | |
|---|---|---|---|
| 1 | Count | ||
| 2 | [email protected] | ||
| 3 | [email protected] | ||
| 4 | [email protected] | ||
| 5 | [email protected] | ||
| 6 | [email protected] |
Answer: =COUNTIF(A2:A6,A2)
Worked example
Five emails in A2:A6; the same list answers both the delete and the review question.
| Formula | Result |
|---|---|
=COUNTIF(A2:A6,A2) | 2 |
=COUNTIF(A2:A6,A3) | 1 |
=COUNTIF($A$2:A4,A4)>1 | TRUE |
=ROWS(UNIQUE(A2:A6)) | 4 |
=ROWS(A2:A6)-ROWS(UNIQUE(A2:A6)) | 1 |
Quick mistake checklist
- Removing rows before deciding which columns form the key.
- Assuming the command can be undone after the workbook is closed.
- Ignoring spaces, nonprinting characters, or inconsistent number types.
Frequently asked questions
Does Remove Duplicates keep the first or last row?
It keeps the first occurrence in the current order. Sort deliberately first if you need the newest or highest-priority record to be first.
Can I remove duplicates without deleting data?
Yes. Use UNIQUE for a separate list, or filter a COUNTIF helper column and review the matches.
Why are obvious duplicates still present?
The compared cells may differ by spaces, hidden characters, type, or another selected column.
Can Excel remove duplicate whole rows?
Yes. Select all table columns in the dialog so only rows with matching values across those columns are removed.
Practice the skill
Continue with a checked FormulaCamp lesson: Duplicates and Text to Columns Free
Open the free lesson