Flag duplicates with COUNTIF
COUNTIF counts how many times a value appears in a column, and wrapping it in IF labels every row whose value appears more than once.
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique")The sheet: Contacts sheet: column A is Email address. You want a label next to each row showing whether that email appears more than once.
How it works, step by step
- 01COUNTIF($A$2:$A$500,A2) counts how many cells in the column equal the email in A2. The count includes A2 itself.
- 02If the count is greater than 1, the email exists on at least one other row.
- 03IF returns "Duplicate" for those rows and "Unique" for the rest.
- 04The $ signs lock the range so it stays the same as you fill the formula down.
The same logic in four tools
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique")=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique")SELECT email,
CASE WHEN COUNT(*) OVER (PARTITION BY email) > 1
THEN 'Duplicate' ELSE 'Unique' END AS status
FROM contacts;import numpy as np
import pandas as pd
# keep=False marks every copy. keep="first" marks only the later copies.
contacts["status"] = np.where(
contacts["email"].duplicated(keep=False), "Duplicate", "Unique"
)Mistakes that break it
- !The first formula labels every copy as Duplicate, including the first one. If you plan to delete duplicates, use the expanding-range version so the first copy stays marked Unique.
- !COUNTIF ignores upper and lower case, so Ana@shop.com and ana@shop.com are duplicates in the sheet but not in pandas or most SQL databases. Normalize with LOWER or .str.lower() first.
- !A trailing space makes two identical-looking emails count as different values. Clean the column with TRIM before checking.
- !COUNTIF treats * and ? as wildcards, and it cannot compare text longer than 255 characters. Long strings return #VALUE!.
Common questions
- How do I highlight duplicates in Excel?
- Select the column, then use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. The COUNTIF formula is for when you need a label or filter column instead.
- How do I count duplicates in a column?
- Count the rows labeled Duplicate with =COUNTIF(B2:B500,"Duplicate"), or in Excel 365 use =ROWS(A2:A500)-ROWS(UNIQUE(A2:A500)) for the number of surplus rows.
- How do I remove duplicates but keep one copy?
- In Excel use Data, Remove Duplicates. In pandas use contacts.drop_duplicates(subset="email"). In SQL use SELECT DISTINCT ON (email) in PostgreSQL or a ROW_NUMBER() filter.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.