Three methods — pick the one that fits your situation.
This permanently deletes duplicate rows from your data. Use it when you want to clean your dataset in place.
If you check all columns, a row is only a duplicate if every column matches. If you check just the Email column, rows with the same email are removed even if other columns differ.
Excel tells you how many duplicates were removed and how many unique values remain.
Remove Duplicates deletes rows from your data. If you might need them back, copy your data to another sheet first.
This adds a column that marks duplicates without deleting anything. Useful when you want to review before removing.
=COUNTIF($A$2:$A$100, A2)
-- Returns 1 if the value is unique, 2+ if it appears multiple times
-- Filter for values > 1 to see all duplicatesReturns a deduplicated list as a formula output. Your original data stays untouched.
=UNIQUE(A2:A100)
-- Spills a list of every distinct value in A2:A100
-- Updates automatically when source data changesUse UNIQUE when you need a clean reference list that updates dynamically — perfect for dropdown lists or summary reports.
Remove Duplicates when you're cleaning data permanently. COUNTIF when you want to review first. UNIQUE when you need a live deduplicated list for another formula or dropdown.
ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.
Start practising free →