Make a Sorted List of Unique Values in Excel

To make a unique sorted list in Excel, type =SORT(UNIQUE(B2:B9)) in an empty cell. UNIQUE keeps each value once, SORT puts the values in A to Z order, and the list updates by itself when the data changes. This guide is for anyone who needs a clean list of customers, products or regions for a summary table, a drop-down or a test question.

The formula for a unique sorted list#

The formula has two functions, one inside the other. Excel works from the inside out:

=SORT(UNIQUE(B2:B9))

1. UNIQUE(B2:B9) looks down the range and returns each value once, in the order it first appears.
2. SORT(...) takes that shorter list and sorts it A to Z (or smallest to largest for numbers).

The result spills: it fills as many cells below as it needs. You type the formula in one cell only. Both functions need Excel 2021, Excel 2024 or Microsoft 365. In older versions you see #NAME?.

All the examples use this order list in A1:C9:

How to build the list step by step#

Pick an empty cell with empty cells below it, type the formula and press Enter. Here it goes in E2:

1. Click E2.
2. Type =SORT(UNIQUE( and select B2:B9. Leave out the header in B1.
3. Type )) to close both functions and press Enter.

Eight rows became five. If you add order 1009 for "Labels" and extend the range, Labels appears between Folders and Pens on its own. The UNIQUE function guide and the SORT function guide list every optional argument. For a full introduction to UNIQUE, see how the UNIQUE function works.

How to sort the unique list Z to A or by count#

SORT takes up to four arguments: =SORT(array, sort_index, sort_order, by_col). To sort Z to A, set sort_order to -1 and leave sort_index empty:

=SORT(UNIQUE(B2:B9),,-1)

The result is Tape, Stapler, Pens, Folders, Binders. The two commas skip sort_index, which then defaults to 1 (the first column).

To put the most common item first, sort by how many times each one appears. SORTBY can sort one list by another:

=SORTBY(UNIQUE(B2:B9),COUNTIF(B2:B9,UNIQUE(B2:B9)),-1)

COUNTIF counts each unique product in the original column: Pens 3, Folders 2 and 1 for the rest. SORTBY then orders the products by those counts, highest first. So Pens comes first, then Folders, then the three products ordered once. For a ranked list of the biggest values instead, read about the top N list with SORT and FILTER.

How to skip blanks in a unique list#

Blank cells in the range appear as a 0 in the result, and SORT puts that 0 at the top. Remove blanks with FILTER before UNIQUE sees them:

=SORT(UNIQUE(FILTER(B2:B20,B2:B20<>"")))

FILTER keeps only the cells that are not empty (<>"" means "not equal to nothing"). This is useful when you point the formula at a longer range, like B2:B20, to leave room for new rows.

Extra spaces cause a similar problem. "Pens" and "Pens " with a trailing space are two different values to UNIQUE. Clean them inside the formula with TRIM: =SORT(UNIQUE(TRIM(B2:B9))). UNIQUE is not case-sensitive, so "pens" and "Pens" already count as one.

How to add counts or totals next to the list#

Once the list is in E2, you can refer to the whole spilled list as E2#. The # is the spill range operator: it means "every cell this formula filled", even when the list grows. In F2, type:

=COUNTIF(B2:B9,E2#)

This spills a count beside each product: Binders 1, Folders 2, Pens 3, Stapler 1, Tape 1. For total quantity, use SUMIF in G2:

=SUMIF(B2:B9,E2#,C2:C9)

That gives Binders 4, Folders 40, Pens 40, Stapler 2, Tape 5. You now have a small summary table that updates with the data. To count how many distinct products there are, use =COUNTA(E2#), which returns 5. The article on counting unique values covers more ways to do that, including older versions.

A sorted unique list also makes a great source for a drop-down. See dynamic drop-down lists that update themselves.

How to make a unique sorted list in older Excel#

Excel 2019 and older don't have UNIQUE or SORT, so you build the list with menu commands. The result is a fixed list that doesn't update.

With Remove Duplicates#

1. Copy the Product column to an empty column.
2. With the copy selected, go to Data > Remove Duplicates and click OK.
3. With the list still selected, go to Data > Sort A to Z.

With Advanced Filter#

1. Click inside the data and go to Data > Advanced (in the Sort & Filter group).
2. Choose Copy to another location, set the List range to B1:B9 and Copy to an empty cell.
3. Tick Unique records only and click OK, then sort the new list A to Z.

Advanced Filter leaves your original data untouched. Remove Duplicates deletes rows from whatever you select, so always run it on a copy. The comparison of UNIQUE vs Remove Duplicates explains when each is the better choice.

Practise it#

A sorted list of distinct values is the first step of many summary tasks in job tests. Practise the UNIQUE function in a live spreadsheet: you type the formula and it checks it instantly. Then practise sorting with the SORT function and combine the two. Try the exercise on this page first: one formula, five products.