Duplicate values clutter data and skew analysis. Excel gives you several ways to find, highlight, and remove duplicates — from a one-click button to formula-based approaches. Here's how to do each.
The quick way: Remove Duplicates button
Excel has a built-in tool that deletes duplicate rows in seconds:
- Select your data range (including headers).
- Go to the Data tab.
- Click Remove Duplicates.
- In the dialog, choose which columns to check. If you check all columns, only fully identical rows are removed. If you check one column (say, Email), rows with duplicate emails are removed even if other columns differ.
- Click OK. Excel deletes the duplicates and tells you how many it removed.
Important: this permanently deletes rows. Work on a copy if you might need the originals, because there's no detailed undo beyond Ctrl+Z immediately after.
Highlighting duplicates first (safer)
Often you want to see duplicates before deleting anything. Use conditional formatting:
- Select the range.
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a color and click OK.
Every duplicated value is now highlighted, so you can review them before deciding what to remove. This is the safer first step — look, then act.
Finding duplicates with a formula
To flag duplicates without altering data, use COUNTIF in a helper column:
=COUNTIF(A:A, A2)>1
Drag this down. It returns TRUE for any value that appears more than once. You can then filter for TRUE to see all duplicates.
To count how many times each value appears:
=COUNTIF(A:A, A2)
Anything above 1 is duplicated.
Extracting a unique list
Sometimes you want a clean list of unique values, keeping the original intact.
Excel 365 / 2021 — the UNIQUE function:
=UNIQUE(A2:A100)
This spills a list of all distinct values automatically, updating as your data changes. It's the cleanest modern approach.
Older versions — Advanced Filter:
- Data > Advanced (in the Sort & Filter group).
- Check "Unique records only."
- Choose to filter in place or copy to another location.
Removing duplicates across multiple columns
If a "duplicate" is defined by a combination of columns (same name AND same date, say), the Remove Duplicates dialog lets you check exactly those columns. Only rows matching on all checked columns are treated as duplicates.
Which method to use
- Just clean the data fast? Data > Remove Duplicates.
- Want to review before deleting? Conditional formatting to highlight first.
- Need to keep the original and see a unique list? UNIQUE function (365) or Advanced Filter.
- Want to flag or count without changing anything? COUNTIF helper column.
The takeaway
For a quick cleanup, Data > Remove Duplicates does the job in a few clicks. But when the data matters, highlight duplicates with conditional formatting first so you can review them, or use UNIQUE to pull a clean list while keeping your original intact. Choosing the right method depends on whether you want to delete, review, or simply identify the duplicates.