How to / Counting
How to remove duplicates in Excel with a formula
Use =UNIQUE(A2:A7) in an empty column. It returns the list with every repeat removed and leaves the original as it is.
Each city once
| A | B | C | |
|---|---|---|---|
| 1 | City | ||
| 2 | Lisbon | Lisbon | |
| 3 | Porto | Porto | |
| 4 | Lisbon | Faro | |
| 5 | Faro | ||
| 6 | Porto | ||
| 7 | Lisbon |
In C2: =UNIQUE(A2:A7) → Lisbon; Porto; Faro
How it works
- UNIQUE keeps the first of each value and drops the rest.
- The result spills down from the cell with the formula, as many rows as it needs.
- It is live: add a new city to the list and it appears in the result.
Variations
Without duplicates, and sorted
A B C 1 City 2 Lisbon Faro 3 Porto Lisbon 4 Lisbon Porto 5 Faro 6 Porto 7 Lisbon In C2:
=SORT(UNIQUE(A2:A7))→ Faro; Lisbon; PortoOnly the values that have no duplicate
A B C 1 City 2 Lisbon Faro 3 Porto 4 Lisbon 5 Faro 6 Porto 7 Lisbon In C2:
=UNIQUE(A2:A7, FALSE, TRUE)→ Faro
Watch out for
- #SPILL! The result needs empty cells below the formula. Clear whatever is in the way.
- Deleting the duplicates for good. UNIQUE makes a clean copy. To delete the repeated rows from the data itself, select it and use Data > Remove Duplicates. That cannot be undone after saving, so keep a copy.
- Older versions of Excel. UNIQUE needs Excel 2021 or Microsoft 365. In older versions, use Data > Remove Duplicates, or Data > Advanced with "Unique records only".
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
- UNIQUE: Removes duplicates from a list. The result spills.
- SORT: Sorts a range. The result spills.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- COUNTIF: Counts the cells that meet a condition.