Data cleaning

Remove duplicates in Excel with a formula

Unlike Data › Remove Duplicates, a formula leaves the original data untouched and updates automatically.

Excel 365/2021 and Google Sheets

The formula

Distinct valuesExcelGoogle Sheets
=UNIQUE(A2:A8)
In C2, spills: [ana@x.com], [ben@x.com], [cara@x.com], [dev@x.com]
Values that appear exactly onceExcel
=FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)=1)
In E2, spills: [cara@x.com], [dev@x.com]
Google Sheets – appear onceGoogle Sheets
=FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)=1)
In G2, spills: [cara@x.com], [dev@x.com]
Excel 2019 and older (fill down) (fill down)Excel
=IFERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($I$1:I1,$A$2:$A$8),0),0)),"")
In I2 → ana@x.com
✓ Tested. All 7 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.

Worked example

Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.

ABCDEFGHI
1Email
2ana@x.comana@x.comcara@x.comcara@x.comana@x.com
3ben@x.comben@x.comdev@x.comdev@x.comben@x.com
4ana@x.comcara@x.comcara@x.com
5cara@x.comdev@x.comdev@x.com
6ben@x.com
7dev@x.com
8ana@x.com

How it works

UNIQUE(A2:A8)
Each value once. Use UNIQUE(A2:C8) for unique rows across columns.
COUNTIF(A2:A8,A2:A8)=1
TRUE for values that occur only once.
COUNTIF($I$1:I1,…)
Older Excel: finds the first value not already listed above.

Common errors and fixes

Duplicates that look identical remain?

Extra spaces or different case. UNIQUE ignores case but not spaces. TRIM first.

Why does the formula return #SPILL!?

Clear the cells below.

Does this work in Google Sheets?

Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.