How to Flag Duplicate Entries with a Formula in Excel

Count how often each entry appears, flag only the repeat registrations, and calculate how many extras to remove — all with COUNTIF.

You will learn to

• Count how many times a value appears in a list
• Flag the second and later occurrences, keeping the first
• Count the surplus duplicate rows

How it works

A formula flag keeps working as new rows arrive, unlike a one-off Remove Duplicates. =COUNTIF($C$2:$C$11,C2) tells you how many times the value in C2 appears in the whole list; anything above 1 is a duplicate.

Usually you want to keep the first occurrence and flag only the repeats. Use an expanding range that ends on the current row — =IF(COUNTIF($C$2:C6,C6)>1,"Repeat","Keep") — so the first appearance counts 1 and later ones count 2, 3 and so on. To know how many surplus rows exist, subtract the number of unique values from the number of rows.

=IF(COUNTIF($C$2:C6,C6)>1,"Repeat","Keep")