Formulyo

Excel 365 + Google Sheets · SORT + UNIQUE

A clean A–Z list in one formula

UNIQUE removes repeats, SORT puts them in order. Perfect for drop-down lists.

The formula

=SORT(UNIQUE(A2:A7))

What each part means

Example

Before
AB
1CityClean list
2Madrid
3Lisbon
4Madrid
5Mexico City
6Lisbon
7Bogotá
After
AB
1CityClean list
2MadridBogotá
3LisbonLisbon
4MadridMadrid
5Mexico CityMexico City
6Lisbon
7Bogotá

Step by step

  1. Click cell B2.
  2. Type the formula: =SORT(UNIQUE(A2:A7))
  3. Press Enter.
  4. The results fill the cells below by themselves (it’s a dynamic array formula). Don’t type anything in them.

Where it works

The same formula in other languages

Get 1 formula a day, free

Plus a free weekly planner template for Excel and Google Sheets. Unsubscribe in 1 click.

✓ Done! Check your inbox to confirm.

Want ready-made templates? See the Formulyo pack →

Keep learning

TRIM + PROPERClean messy names in one goLOWERMake any text lowercaseSUBSTITUTEReplace any character everywhereTEXTSPLITSplit text into columns with a formulaCOUNTBLANKFind missing data fastMIDTake text from the middle