Excel 365 + Google Sheets · UNIQUE
Remove duplicates without deleting
Returns each value only once. Your original list stays untouched.
The formula
=UNIQUE(A2:A7)What each part means
A2:A7The list with duplicates
Example
| A | B | |
|---|---|---|
| 1 | City | Unique |
| 2 | Madrid | |
| 3 | Lisbon | |
| 4 | Madrid | |
| 5 | Mexico City | |
| 6 | Lisbon | |
| 7 | Bogotá |
| A | B | |
|---|---|---|
| 1 | City | Unique |
| 2 | Madrid | Madrid |
| 3 | Lisbon | Lisbon |
| 4 | Madrid | Mexico City |
| 5 | Mexico City | Bogotá |
| 6 | Lisbon | |
| 7 | Bogotá |
Step by step
- Click cell B2.
- Type the formula:
=UNIQUE(A2:A7) - Press Enter.
- The results fill the cells below by themselves (it’s a dynamic array formula). Don’t type anything in them.
Where it works
- Excel 365 and Excel 2021 or newer
- Google Sheets
- Excel for the web (free)