Cellwise

Excel guide · 3 min read

How to remove duplicates in Excel

Select your data, go to Data > Remove Duplicates, tick the columns that must match, and click OK. Excel deletes the repeated rows and keeps the first one it finds. To keep your original list untouched, use =UNIQUE(A2:A9) in Excel 365 or 2021 instead.

Sample data

Every formula on this page was calculated live against this sheet.

ABC
1EmailNamePlan
2maria@northwind.comMaria ChenPro
3joe@fabrikam.comJoe PatelFree
4maria@northwind.comMaria ChenPro
5sam@contoso.comSam RiveraTeam
6joe@fabrikam.comJoe PatelPro
7lena@adatum.comLena FischerFree
8sam@contoso.comSam RiveraTeam
9ravi@tailspin.comRavi KumarPro

Method 1: The Remove Duplicates button (any version)

This is the fastest way and it works in every modern version of Excel. It changes your data in place, so make a copy of the sheet first if you might need the duplicates later.

The key decision is which columns to tick. Tick only Email and Excel treats rows 3 and 6 as duplicates, even though Joe has a different plan in each. Tick all three columns and those two rows both survive, because they are not identical.

  1. Click any cell inside your data.
  2. Go to Data > Data Tools > Remove Duplicates.
  3. Leave "My data has headers" ticked if row 1 holds column names.
  4. Tick the columns that must all match for a row to count as a duplicate. Click Unselect All first if you only want one or two.
  5. Click OK. Excel tells you how many duplicate values were removed and how many unique values remain.

Method 2: The UNIQUE function (Excel 365 and 2021)

UNIQUE builds a clean list in a new spot and leaves the source alone. It updates on its own when the source changes, which makes it the better choice for lists you keep adding to.

Point it at one column for a unique list of values, or at several columns to keep only unique rows.

fx=UNIQUE(A2:A9)

Result: maria@northwind.com spills 5×1

maria@northwind.com
joe@fabrikam.com
sam@contoso.com
lena@adatum.com
ravi@tailspin.com

Spills the five distinct emails, each listed once, in the order they first appear.

Remove duplicate rows across several columns with UNIQUE

Give UNIQUE the whole block and it compares entire rows. This is the formula version of ticking every column in the Remove Duplicates dialog.

Wrap it in SORT if you want the result alphabetical: =SORT(UNIQUE(A2:C9)).

fx=UNIQUE(A2:C9)

Result: maria@northwind.com spills 6×3

maria@northwind.comMaria ChenPro
joe@fabrikam.comJoe PatelFree
sam@contoso.comSam RiveraTeam
joe@fabrikam.comJoe PatelPro
lena@adatum.comLena FischerFree
ravi@tailspin.comRavi KumarPro

Returns six rows. Joe appears twice because his Free and Pro rows are different.

Method 3: Advanced Filter (older Excel, keeps the original)

If you are on Excel 2019 or earlier and want to keep the original list, Advanced Filter can copy only the unique rows to another place. The copy is static, so rerun it after the data changes.

  1. Click inside your data.
  2. Go to Data > Sort & Filter > Advanced.
  3. Choose "Copy to another location".
  4. Check that List range covers your data, then pick a single cell in Copy to.
  5. Tick "Unique records only" and click OK.

Which method should you pick?

Use Remove Duplicates for a one-time cleanup of a list you own. Use UNIQUE when the list keeps growing or when other people need to see the original data. Use Advanced Filter only when UNIQUE is not available and you must keep the source intact.

If you want to look at the duplicates before you delete anything, highlight them first. The find duplicates guide shows how.

Common mistakes

Duplicates that differ only by spaces are not removed

Excel sees "joe@fabrikam.com " with a trailing space as a different value. Clean the column with TRIM first, then remove duplicates.

Ticking the wrong columns

Remove Duplicates only compares the ticked columns. Tick one column and you can lose rows whose other columns held unique details.

Losing data you needed

Remove Duplicates deletes rows for good once you save. Copy the sheet first, or use UNIQUE so the original stays put. Ctrl+Z undoes it if you have not closed the file.

Expecting a case-sensitive match

Both Remove Duplicates and UNIQUE ignore case, so "SAM@contoso.com" and "sam@contoso.com" count as the same value.

FAQ

Which duplicate does Excel keep?

Remove Duplicates keeps the first occurrence, counting from the top, and deletes the rest. Sort your data first if you want a particular row, such as the newest one, to be kept.

How do I remove duplicates but keep one?

That is exactly what Remove Duplicates and UNIQUE do. Each value is kept once. If you want to drop every value that appears more than once, use =UNIQUE(A2:A9,,TRUE) in Excel 365, which returns only values that occur exactly once.

Why is Remove Duplicates greyed out?

The sheet is probably protected, shared, or you are editing a cell. Press Esc, unprotect the sheet with Review > Unprotect Sheet, and try again. It also cannot run inside a PivotTable or on outlined subtotal data.

Can I remove duplicates without deleting rows?

Yes. Use =UNIQUE() in a new column or Advanced Filter with "Copy to another location". Both give you a clean list while the original data stays where it was.

Functions used

Keep learning

Updated 2026-10-31