How to / Counting

How to find duplicates in Excel with a formula

Next to the list, use =COUNTIF($A$2:$A$7, A2)>1 and fill it down. It shows TRUE on every row whose value appears more than once.

Is this city in the list more than once?

ABC
1City
2LisbonTRUE
3Porto
4Lisbon
5Faro
6Porto
7Lisbon

In C2: =COUNTIF($A$2:$A$7, A2)>1 → TRUE

Lisbon appears three times.

How it works

  1. COUNTIF counts how many times the value in A2 appears in the whole list.
  2. A count above 1 means the value is repeated.
  3. The $ signs lock the list, so it stays in place while A2 becomes A3, A4 and so on as the formula is filled down.

Variations

  1. A label in place of TRUE or FALSE

    ABC
    1City
    2LisbonDuplicate
    3Porto
    4Lisbon
    5Faro
    6Porto
    7Lisbon

    In C2: =IF(COUNTIF($A$2:$A$7, A2)>1, "Duplicate", "") → Duplicate

  2. Mark only the repeats, not the first one

    ABC
    1City
    2LisbonRepeat
    3Porto
    4Lisbon
    5Faro
    6Porto
    7Lisbon

    In C2: =IF(COUNTIF($A$2:A4, A4)>1, "Repeat", "First") → Repeat

    The range grows as the formula goes down, so each row only looks at the rows above it. A4 is the second Lisbon.

  3. How many values are repeated

    ABC
    1City
    2Lisbon2
    3Porto
    4Lisbon
    5Faro
    6Porto
    7Lisbon

    In C2: =SUMPRODUCT((COUNTIF(A2:A7, A2:A7)>1)/COUNTIF(A2:A7, A2:A7)) → 2

    Lisbon and Porto.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related