SheetSense

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.

fx
=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

  1. 01COUNTIF($A$2:$A$500,A2) counts how many cells in the column equal the email in A2. The count includes A2 itself.
  2. 02If the count is greater than 1, the email exists on at least one other row.
  3. 03IF returns "Duplicate" for those rows and "Unique" for the rest.
  4. 04The $ signs lock the range so it stays the same as you fill the formula down.

The same logic in four tools

Excel
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique")
Google Sheets
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique")
SQL
SELECT email,
       CASE WHEN COUNT(*) OVER (PARTITION BY email) > 1
            THEN 'Duplicate' ELSE 'Unique' END AS status
FROM contacts;
Python (pandas)
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.