How to Find and Remove Duplicates in Excel

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016 (UNIQUE needs Microsoft 365 or Excel 2021)

Duplicate records cause real problems: a customer receives the same email twice, an invoice is paid twice, or stock counts are inflated. Excel offers several ways to deal with duplicates. Some only highlight them so you can decide what to do, some delete them permanently, and some produce a separate list of unique values while leaving the original untouched. Choosing the right method depends on whether you need to review the duplicates first and whether the source data must stay as it is.

First, decide what counts as a duplicate

Two rows can be duplicates in different senses. In a customer list, two rows with the same email address are probably the same person, even if the phone numbers differ. In a sales list, two rows with the same customer are normal; only rows with the same invoice number and the same product are suspicious. Before you remove anything, decide which column or combination of columns identifies a record. That decision drives every method below.

It is also wise to work on a copy. Right-click the sheet tab, choose Move or Copy, tick Create a copy and work there until you are happy with the result.

Highlight duplicates with conditional formatting

To see duplicates without changing anything, select the column, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a colour and click OK. Every value that appears more than once is shaded. You can then use the column filter and choose Filter by Color to show only the duplicates. The conditional formatting guide explains more options.

This rule looks at one column at a time. To highlight rows where a combination of columns repeats, use a formula rule instead, as described next.

Flag duplicates with COUNTIF and COUNTIFS

A helper column gives you more control. Next to a list of email addresses in column B, enter:

=COUNTIF($B$2:$B$1000,B2)

Any result greater than 1 is a duplicate. To mark only the second and later occurrences, so that the first one is kept, use an expanding range:

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

Because only the start of the range is locked, the range grows as the formula is copied down, and each row checks only the rows above it. For combinations of columns, use COUNTIFS: =COUNTIFS($A$2:A2,A2,$D$2:D2,D2)>1 flags a repeated invoice number and product pair. Filter the helper column for "Duplicate", review the rows and delete them if they are genuine repeats. See SUMIFS and COUNTIFS for more on how these functions work.

Delete duplicates with Remove Duplicates

When you are confident, Remove Duplicates is the quickest method:

  1. Click any cell in the data.
  2. Go to Data > Data Tools > Remove Duplicates (Alt, A, M).
  3. Tick My data has headers.
  4. Untick every column, then tick only the columns that identify a duplicate, for example Email.
  5. Click OK. Excel reports how many duplicates were removed and how many unique values remain.

Excel keeps the first occurrence and deletes the later ones, entire rows included. If the version you want to keep is the most recent one, sort the data first so the newest record is at the top, for example by date from newest to oldest. Remember that this deletes data. If you make a mistake, press Ctrl + Z straight away.

List unique values with UNIQUE

In Microsoft 365 and Excel 2021, the UNIQUE function returns a list of distinct values without touching the original data:

The result spills into the cells below and updates automatically when the source changes. Make sure there is empty space below the formula, or you will see #SPILL!; the Excel errors guide explains why.

Advanced Filter for older versions

If you do not have UNIQUE, use Advanced Filter to copy unique records elsewhere. Click in the data and choose Data > Sort & Filter > Advanced. Select Copy to another location, check the list range, choose a single cell in an empty area for Copy to and tick Unique records only. The unique list is pasted as fixed values, so repeat the process if the data changes.

Clean the data before you compare

Excel sees "Al Noor Trading" and "Al Noor Trading " (with a trailing space) as different values, so near-duplicates slip through. Before checking for duplicates:

Matching is not case-sensitive, so "AHMED" and "Ahmed" are already treated as the same.

Prevent new duplicates

Once the list is clean, stop duplicates coming back with data validation. Select the column, open Data > Data Validation, choose Custom and enter =COUNTIF($B$2:$B$1000,B2)=1. Anyone who types an existing value gets an error. The data validation guide shows how to write a clear error message.

Summary

Back to: all Excel tutorials.