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?
| A | B | C | |
|---|---|---|---|
| 1 | City | ||
| 2 | Lisbon | TRUE | |
| 3 | Porto | ||
| 4 | Lisbon | ||
| 5 | Faro | ||
| 6 | Porto | ||
| 7 | Lisbon |
In C2: =COUNTIF($A$2:$A$7, A2)>1 → TRUE
Lisbon appears three times.
How it works
- COUNTIF counts how many times the value in
A2appears in the whole list. - A count above 1 means the value is repeated.
- The $ signs lock the list, so it stays in place while
A2becomesA3,A4and so on as the formula is filled down.
Variations
A label in place of TRUE or FALSE
A B C 1 City 2 Lisbon Duplicate 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=IF(COUNTIF($A$2:$A$7, A2)>1, "Duplicate", "")→ DuplicateMark only the repeats, not the first one
A B C 1 City 2 Lisbon Repeat 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=IF(COUNTIF($A$2:A4, A4)>1, "Repeat", "First")→ RepeatThe range grows as the formula goes down, so each row only looks at the rows above it. A4 is the second Lisbon.
How many values are repeated
A B C 1 City 2 Lisbon 2 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=SUMPRODUCT((COUNTIF(A2:A7, A2:A7)>1)/COUNTIF(A2:A7, A2:A7))→ 2Lisbon and Porto.
Watch out for
- Forgetting the $ signs. Without them the list slides down with the formula, and rows near the bottom check a range that no longer starts at the top.
- Highlighting instead. To colour duplicates with no formula at all, select the list and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
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
- COUNTIF: Counts the cells that meet a condition.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.
- UNIQUE: Removes duplicates from a list. The result spills.