The SORT + UNIQUE guide
=SORT(UNIQUE(B2:B11)) lists each value in B2:B11 once, in alphabetical order, and spills the list below. =SORT(A2:C11, 3, -1) sorts a whole table by its 3rd column, largest first. Both work in Excel 2021, Excel for Microsoft 365 and Excel for the web.
The syntax
=SORT(array, [sort_index], [sort_order], [by_col])
- sort_index: which column of the array to sort by, counted from the array's first column. Default 1.
- sort_order: 1 for A to Z and smallest first (the default), -1 for Z to A and largest first. Any other number gives #VALUE!.
- by_col: TRUE sorts columns instead of rows. Rarely needed.
SORT around UNIQUE
UNIQUE lists values in the order they first appear. Wrap it in SORT to put that list in order: =SORT(UNIQUE(B2:B11)). =UNIQUE(SORT(B2:B11)) gives the same list. Add a new rep to the data and the list picks it up in the right place.
Sort a table by one column
Give SORT every column and say which one to sort by. In A2:C11 the amount is the 3rd column, so =SORT(A2:C11, 3, -1) puts the largest order first, with its order number and rep still next to it. Sorting C2:C11 on its own gives the amounts in order, but you can no longer tell whose they are.
SORT(…, -1) gives #VALUE!
The direction is the third argument. In =SORT(A2:C11, -1) the -1 lands in sort_index, and there's no column -1, so Excel shows #VALUE!. With one column, =SORT(UNIQUE(B2:B11), , -1) or =SORT(UNIQUE(B2:B11), 1, -1) sorts Z to A.
Common mistakes
- The header in the range. SORT treats every row as data, so "Amount" gets sorted in with the numbers. Start at row 2.
- The wrong sort_index. 1 sorts by the order number, which is often the order the data is already in.
- Blank cells. UNIQUE turns a blank into 0, and SORT puts it first. Leave blanks out of the range, or filter them:
=SORT(UNIQUE(FILTER(B2:B20, B2:B20<>""))). - Something in the way. The list spills, so the cells below must be empty, or you get #SPILL!.
SORT ignores capitals, and rows with the same value keep the order they had. To pick rows by a condition first, see FILTER.
FAQ
- How do I get a sorted list of unique values in Excel?
- Use =SORT(UNIQUE(range)) in Excel 2021, Microsoft 365 or Excel for the web. The list spills below the formula and updates when the data changes.
- How do I sort largest to smallest with the SORT function?
- Give -1 as the third argument: =SORT(A2:C11,3,-1) sorts by the 3rd column, largest first.
- Why does SORT return #VALUE!?
- Usually the -1 is in the second argument, sort_index, or sort_index is larger than the number of columns. The direction is the third argument.
- Can SORT sort by more than one column?
- Yes, with lists of columns and orders, or with SORTBY. For one key column, SORT(array, column, -1) is enough.



































































