Home › Guides

How to Remove Duplicates in Excel

There are three common ways to deal with duplicate rows in Excel. Which one you pick depends on whether you want to delete them, list unique values, or just find them first.

1. The Remove Duplicates button (deletes rows)

1. Select your data, including headers.
2. Go to Data → Remove Duplicates.
3. Tick the columns that define a duplicate, then click OK.

This permanently deletes rows, so work on a copy if you need the original.

2. UNIQUE (keeps the original)

=UNIQUE(B2:B7)

In Excel 365 this returns a new list with each value once. The source data isn't touched.

3. Flag duplicates with COUNTIF (find before deleting)

Interviewers like this one because it shows you understand the logic. Count how many times a value has appeared so far. If it's more than 1, it's a repeat.

=IF(COUNTIF($B$2:B2,B2)>1,"Duplicate","")

The range $B$2:B2 grows as you copy the formula down, so only earlier rows count.

Practice