Excel can remove duplicates with a menu command, but formulas are better when you want a live list that updates as the source data changes.
The right formula depends on whether you need every distinct value, one copy of each row, or a flag that identifies repeated entries.
Which Excel formula removes duplicates?
In Excel for Microsoft 365 and Excel 2021 or later, use =UNIQUE(A2:A10) to return each distinct value from the range once.
The results spill into cells below the formula automatically, so make sure that area is empty.
For example, if A2:A7 contains Apple, Banana, Apple, Pear, Banana, and Orange, the formula returns:
- Apple
- Banana
- Pear
- Orange
UNIQUE creates a separate result; it does not delete or alter the original entries.
This makes it useful for reports, dropdown source lists, and summaries that should refresh when the source range changes.
Return unique values from a column
For a fixed range, enter this formula in an empty cell, such as C2:
=UNIQUE(A2:A100)
To include only values that occur exactly once, use the third argument:
=UNIQUE(A2:A100,,TRUE)
In this form, TRUE means “return only values that appear once.” For example, if Oak appears once and Pine appears three times, the result contains Oak but excludes Pine.
This is different from the default, which returns one copy of Pine.
To sort the distinct results, wrap UNIQUE in SORT:
=SORT(UNIQUE(A2:A100))
UNIQUE preserves the order of first appearance in the source.
SORT changes that order to alphabetical or numerical order, which can make lookup lists easier to scan.
Return unique rows from multiple columns
When duplicates are complete rows rather than repeated entries in a single column, select the full range in the formula.
For data in A2:C10, enter:
=UNIQUE(A2:C10)
Excel compares the values across each row.
If two rows have the same values in all three columns, only one is returned.
Rows that share a name but differ in another column remain separate.
For instance, two records for “Jordan Lee” are not duplicates if one has a different department.
To identify duplicates by only one column, such as an email address, create a distinct email list with =UNIQUE(B2:B100) or use a helper formula to mark repeated emails.
Remove duplicates while keeping the first occurrence
In older Excel versions without UNIQUE, a helper column can mark the first occurrence of each value.
If the data is in A2:A10, enter this in B2 and fill it down:
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
The expanding range $A$2:A2 grows as the formula is copied downward.
COUNTIF counts how many times the current value has appeared so far.
The first occurrence is returned, while later repeats produce a blank.
For example, with Apple, Banana, Apple in A2:A4, the helper column returns Apple, Banana, and a blank.
To make a compact list, filter column B to hide blanks, or copy the results and paste values elsewhere before removing blank cells.
This approach works in many older versions of Excel, including Excel 2016 and Excel 2019.
It does not dynamically spill a compact list by itself; it marks repeated values so you can filter or copy the results.
Flag duplicates without deleting them
To label each occurrence after the first as a duplicate, use this formula in B2 and fill it down:
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")
The comparison operator > checks whether the running count exceeds 1.
This keeps the original data intact while making repeated values visible.
You can then filter for “Duplicate” before deciding what to remove.
To flag every value that appears more than once, including its first occurrence, use:
=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","Unique")
This formula checks A2 against the whole list each time.
Unlike the running-count formula, it labels the first appearance as a duplicate too whenever the value occurs elsewhere in the range.
Use a case-sensitive duplicate check
COUNTIF is not case-sensitive: it generally treats ABC and abc as matching text.
If capitalization distinguishes values in your data, use SUMPRODUCT with EXACT.
For A2:A10, enter this in B2 and fill down:
=IF(SUMPRODUCT(--EXACT(A2,$A$2:A2))>1,"Duplicate","First occurrence")
EXACT compares text with case sensitivity, and SUMPRODUCT counts the matching results.
The double unary operator, --, converts TRUE and FALSE values into 1 and 0 so they can be added.
This formula can be more demanding on large ranges, so use a bounded data range instead of whole-column references when performance matters.
Handle blanks and common formula problems
UNIQUE can return a blank when the source includes empty cells.
To exclude blanks in Microsoft 365 or Excel 2021 and later, combine it with FILTER:
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
Here, <> means “not equal to.” If every cell in the source is blank, FILTER may return an error unless you provide its optional fallback:
=UNIQUE(FILTER(A2:A100,A2:A100<>"",""))
If UNIQUE returns #SPILL!, check whether cells in the output area contain data, merged cells, or another obstruction.
Clear the blocked cells or move the formula to an open area.
If Excel displays #NAME?, your version may not support UNIQUE, or the function name may be misspelled.
Leading or trailing spaces can make entries that look identical behave as different text.
A helper column using =TRIM(A2) can remove ordinary extra spaces before you generate a unique list.
TRIM does not remove every nonprinting character, so imported data may also need cleanup with CLEAN or a targeted SUBSTITUTE formula.
Choose the method that fits your version
- Excel for Microsoft 365 or Excel 2021 and later: Use UNIQUE for a live list of distinct values or rows.
Combine it with SORT or FILTER as needed. - Older Excel versions: Use COUNTIF in a helper column to keep or flag the first occurrence, then filter the results.
- For permanent deletion: Use Excel’s Data > Remove Duplicates command on a copy of the data.
It changes the selected source range, while a formula produces results separately.
Before removing duplicates permanently, decide which columns define a duplicate and whether the first or last record should be retained.
A repeated email may identify duplicate contacts, while identical product names with different sizes may represent separate records.