Remove Duplicates in Excel: 5 Easy Methods (UNIQUE, Power Query and More)

Duplicate rows are one of the most common data problems in Excel. A customer appears twice, an order is copied by mistake, and suddenly your totals are wrong. The good news: Excel gives you several ways to find and remove duplicates, from one click to a fully automatic formula.

This guide shows 5 methods, explains when to use each, and covers the traps (extra spaces, case, partial duplicates) that make duplicates hard to catch.

Quick answer: Select your data, go to Data → Remove Duplicates, tick the columns that define a duplicate and click OK. To keep the original data untouched, use the formula =UNIQUE(A2:C100) instead (Excel 365 and 2021).

Before you start: make a backup

Method 1 deletes rows permanently (apart from Undo). Copy the sheet first (right-click the sheet tab → Move or Copy → Create a copy), or use one of the non-destructive methods.

Method 1: Remove Duplicates button (fastest)

1. Click any cell inside your data.
2. Go to Data → Data Tools → Remove Duplicates.
3. Tick My data has headers if row 1 has column names.
4. Choose the columns that must match for a row to count as a duplicate.
5. Click OK. Excel tells you how many duplicates were removed and how many unique values remain.

Which columns to tick? Tick all columns to remove only rows that are exact copies. Tick only Email to keep one row per email address, even if other columns differ. Excel keeps the first occurrence and deletes the rest, so sort your data first if you want to keep, for example, the latest record.

Method 2: UNIQUE function (keeps the original)

=UNIQUE(A2:A100)

Returns each value once, in a spilled list that updates when the source changes. It works on several columns too:

=UNIQUE(A2:C100)

This returns unique rows across the three columns. Two more useful versions:

=SORT(UNIQUE(A2:A100))          sorted unique list
=UNIQUE(A2:A100, FALSE, TRUE)   values that appear exactly once

The third argument TRUE (exactly_once) returns only values that are never repeated, which is handy for finding one-time customers. UNIQUE is available in Excel for Microsoft 365, Excel 2021 and later.

Method 3: Highlight duplicates first (find, don't delete)

1. Select the column, for example A2:A100.
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Pick a colour and click OK.

Every value that appears more than once is coloured. Then use Data → Filter → Filter by Color to review them. This is the safest method when you need to check each duplicate before deleting it.

Flag only the second and later copies

Add a helper column with this formula in B2 and copy it down:

=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "")

The range grows row by row, so the first occurrence stays blank and only the repeats are marked. Filter the helper column on "Duplicate" and delete those rows. To check two columns together, use COUNTIFS($A$2:A2, A2, $B$2:B2, B2).

Method 4: Advanced Filter (works in every Excel version)

1. Click inside your data and go to Data → Sort & Filter → Advanced.
2. Choose Copy to another location.
3. Check the List range, pick a cell in Copy to.
4. Tick Unique records only and click OK.

Excel copies one row for each unique record and leaves the original alone. Use this if your Excel does not have the UNIQUE function.

Method 5: Power Query (best for repeated cleaning)

1. Select the data and go to Data → From Table/Range.
2. In Power Query, select the column(s) that define a duplicate (hold Ctrl for several).
3. Go to Home → Remove Rows → Remove Duplicates.
4. Click Close & Load.

Next month, paste the new data into the source table and click Data → Refresh All. The cleaning runs again automatically. Note that Power Query is case-sensitive: "Asha" and "ASHA" are different. Add a Transform → Format → lowercase step first if case should not matter.

Which method should you use?

Method	Changes original?	Updates automatically?	Best for
Remove Duplicates button	Yes	No	Quick one-time clean-up
UNIQUE function	No	Yes	Live unique lists, drop-downs
Conditional Formatting	No	Yes	Reviewing before deleting
Advanced Filter	No (copy)	No	Older Excel versions
Power Query	No	On refresh	Monthly or repeated data

Why are my duplicates not removed?

Extra spaces

"Delhi" and "Delhi " (with a trailing space) look the same but are different values. Clean them first with =TRIM(A2) in a helper column, or use Transform → Format → Trim in Power Query.

Numbers stored as text

101 as a number and "101" as text are not equal. Convert the column with Data → Text to Columns → Finish, or multiply by 1.

Different case

The Remove Duplicates button and UNIQUE ignore case, so "asha" and "Asha" count as the same. Power Query does not ignore case.

Hidden characters

Data copied from websites can contain non-breaking spaces. Use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).

Frequently asked questions

How do I remove duplicates but keep the latest record?

Sort by date from newest to oldest first, then use Remove Duplicates. Excel keeps the first row it sees, which is now the newest.

How do I count unique values in Excel?

Use =COUNTA(UNIQUE(A2:A100)) in Excel 365, or =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) in older versions (the range must not contain blanks).

Can I undo Remove Duplicates?

Yes, with Ctrl + Z, as long as you have not saved and closed the file.

How do I remove duplicates in Power BI?

The same way as Method 5: in Power Query Editor, select the column and choose Remove Rows → Remove Duplicates.

Practice task

Take a customer list with Name, Email and City. Highlight duplicate emails with Conditional Formatting, add the COUNTIF helper column to flag the repeats, then build a clean live list with =UNIQUE(A2:C100). Compare the row counts of the original and the clean list.

Next: pull matching rows with the Excel FILTER function, and summarise your clean data with an Excel pivot table.