Data cleanup
How to Remove Duplicates in Excel (3 Simple Ways)
Duplicate rows creep into every spreadsheet. Excel gives you three ways to deal with them: a live UNIQUE formula, the one-click Remove Duplicates button, and COUNTIF to flag dupes without deleting anything.
This guide shows when to use each. UNIQUE is best for a refreshable list, the ribbon button for a one-time cleanup, and COUNTIF when you only want to highlight.
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once]) | Argument | Required | What it does |
|---|---|---|
array | Required | The range or column to return distinct values from. The result spills automatically. |
by_col | Optional | FALSE (default) compares rows; TRUE compares columns. |
exactly_once | Optional | TRUE returns only items that appear exactly once; FALSE (default) returns one of each. |
Note: =UNIQUE spills a live list in Microsoft 365 and Google Sheets. For older Excel use Data > Remove Duplicates, and use COUNTIF to flag dupes without deleting.
Examples
Real, copy-paste-ready formulas.
1. Get a clean list with UNIQUE
=UNIQUE(A2:A500) UNIQUE spills a de-duplicated list that updates automatically as the source data changes. It keeps one of each value and leaves the original column untouched.
2. Flag duplicates without deleting
=IF(COUNTIF($A$2:$A$500, A2)>1, "Duplicate", "Unique") COUNTIF counts how many times each value appears; if it is more than once the row is flagged. Lock the range with $ so you can fill the formula down.
3. Count how many distinct values there are
=COUNTA(UNIQUE(A2:A500)) UNIQUE returns the distinct list and COUNTA counts the items in it, giving the number of unique customers in one formula.
How to write UNIQUE step by step
- 1
Modern Excel or Sheets: click an empty cell, type =UNIQUE( select your range, close the bracket ), and the de-duplicated list spills down.
- 2
Older Excel (one-time cleanup): select your data, open the Data tab, and click Remove Duplicates.
- 3
In the dialog, tick the columns that define a duplicate, then click OK. Excel deletes the extra rows and keeps the first of each.
- 4
To highlight instead of delete, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- 5
To flag in a helper column, use =COUNTIF(range, cell)>1 and filter on the result.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#SPILL! | UNIQUE needs empty cells to spill into but something is blocking the range. | Clear or move whatever sits in the cells below and to the right of the formula. |
#NAME? | UNIQUE is not available in your Excel version (it needs Microsoft 365 or Excel 2021+). | Use the Data > Remove Duplicates button instead. |
Remove Duplicates greyed out | The cursor is not inside the data range. | Click a single cell within the data first, then open Data > Remove Duplicates. |
Case treated as same | Both UNIQUE and Remove Duplicates see "ABC" and "abc" as identical. | Add a helper column with EXACT() or UPPER() to control case-sensitive de-duping. |
Frequently asked questions
How do I remove duplicates but keep one?
Both Data > Remove Duplicates and =UNIQUE keep the first occurrence of each value and drop the rest, so you always retain one copy.
How do I remove duplicates based on one column?
In the Remove Duplicates dialog, tick only that one column. Excel then treats two rows as duplicates whenever that column matches, ignoring the others.
How do I find duplicates without deleting them?
Use Conditional Formatting > Highlight Cells Rules > Duplicate Values, or add =COUNTIF(range, cell)>1 in a helper column to flag them.
Does UNIQUE work in Google Sheets?
Yes. UNIQUE works the same way in Google Sheets and spills a live, auto-updating list.