Before you clean a mailing list, it helps to see the repeats. This marks every entry that shows up more than once.
The formula
=IF(COUNTIF(A:A,A2)>1,"Duplicate","") in column B
What each part does
COUNTIF(A:A,A2)
Counts how many times the value in this row appears in the whole column.
>1
More than one occurrence means the value is repeated.
"Duplicate",""
What to show when it is a duplicate, and when it is not.
How it works, and what to watch for
COUNTIF counts how many times each value appears across the whole column. More than one means it is a repeat.
This flags every copy, including the first. If you would rather keep the first and mark only the later ones, the count can be made to look only at the rows above.
Near-matches like "Jon" and "John" will not be caught, since they are genuinely different text. For those you need fuzzy matching, which lives in Power Query.
Questions
How do I mark the repeats but keep the first one?
Point the count at the rows from the top down to the current row: =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate",""). The first time a value appears it counts as one, so it stays unmarked.
How do I just remove duplicates?
Select the column and use Data > Remove Duplicates. A formula is better when you want to see them first, before anything is deleted.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
