3 ways to remove duplicates in Excel
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
Three EASY Ways to Find and Remove Duplicates in Excel
Leila Gharani
Open on YouTube (opens in a new tab)
Duplicate rows creep into almost every dataset. Here are three ways to remove them, from quickest to most repeatable.
1. The Remove Duplicates button
Select your data, go to Data → Remove Duplicates, choose which columns to check, and click OK. Fast and permanent — it deletes the extra rows in place.
Use it when: you want a quick, one-off cleanup and don’t need to keep the original.
2. The UNIQUE function
On modern Excel, UNIQUE returns a de-duplicated list that updates
automatically as the source changes:
=UNIQUE(A2:A100)Use it when: you want a live list that stays in sync with the source data.
3. Power Query
Load the data into Power Query (Data → From Table/Range), right-click the column header, and choose Remove Duplicates. Then load it back to a sheet.
Use it when: this is a recurring task. Power Query records the steps, so next time you just refresh.
Which should you pick?
For a one-time fix, the button. For a formula that stays current, UNIQUE.
For a report you rebuild every week, Power Query — set it up once and refresh.
Go deeper with this skill
Turn messy imported data into a reliable table that formulas, pivots, and reports can use safely. For this article, the goal is to practice: Remove duplicate rows using the built-in button, UNIQUE, or Power Query — and how to pick the right one.
Practice workbook setup
Copy messy sample data to a raw-data sheet and keep the original untouched while you clean a separate working copy.
Practice workflow
- Identify the expected columns and data types before changing anything.
- Remove obvious blanks and duplicates, then check whether any removals need business approval.
- Standardize spacing, casing, dates, and number formats.
- Create a small before-and-after table so the cleanup can be reviewed.
Quality checks
- The row count before and after cleanup is explainable.
- Key columns have no unexpected blanks.
- Dates, IDs, and amounts are stored as the correct data type.
Common mistakes
- Cleaning directly over the only copy of the data.
- Removing duplicates without deciding which columns define a duplicate.
- Fixing visible rows while hidden filtered rows stay messy.
Next actions
- Convert the cleaned range into an Excel Table.
- Repeat the same cleanup with Power Query if the data will arrive again.
Formula focus: test UNIQUE() with normal rows, blank inputs, and at least one edge case.
