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:
- Click any cell in the data.
- Go to Data > Data Tools > Remove Duplicates (Alt, A, M).
- Tick My data has headers.
- Untick every column, then tick only the columns that identify a duplicate, for example Email.
- 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:
=UNIQUE(B2:B1000)lists each value once.=SORT(UNIQUE(B2:B1000))lists them in alphabetical order.=UNIQUE(A2:C1000)returns unique combinations of three columns.=UNIQUE(B2:B1000,,TRUE)returns only values that appear exactly once, which is a quick way to find the odd ones out.=COUNTA(UNIQUE(B2:B1000))counts how many distinct customers you have.
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:
- Remove extra spaces with TRIM and standardise case with PROPER or LOWER. See text functions.
- Make sure numbers and dates are real numbers, not text.
- Standardise common variations, such as "LLC" and "L.L.C.", with Find and Replace (Ctrl + H).
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
- Decide which columns define a duplicate before you start.
- Conditional formatting highlights duplicates without changing data.
- A COUNTIF helper column lets you review and keep the first occurrence.
- Remove Duplicates deletes repeats permanently; sort first to control which row is kept.
- UNIQUE produces a live list of distinct values; Advanced Filter works in older versions.
Back to: all Excel tutorials.