The SORT function in Excel

Q: How do I sort from largest to smallest with a formula?

Set sort_order to -1: =SORT(A2:D7,4,-1) sorts by the fourth column, highest first.

Q: How do I sort by two columns?

Give arrays for the index and order: =SORT(A2:D7,{2,3},{1,-1}) sorts by column 2 A to Z, then by column 3 highest first. SORTBY is another option.

SORT returns a sorted copy of a range, and the results spill into the cells below and to the right. The original data stays exactly where it is, and the sorted copy updates whenever the data changes. Use it for live leaderboards, alphabetical lists and "top performers" views that never need re-sorting by hand.

Syntax

=SORT(array, [sort_index], [sort_order], [by_col])

array	The range to sort, like `A2:C7`. Leave out the header row.
sort_index	Optional. Which column of `array` to sort by: `1` is its first column (the default), `2` the second, and so on.
sort_order	Optional. `1` for ascending, A to Z or smallest first (the default). `-1` for descending.
by_col	Optional. `FALSE` sorts rows (the default). `TRUE` sorts columns left to right instead.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In F2, use SORT to sort the leaderboard in A2:C7 by Score (its 3rd column), highest first. The results will spill from F2 to H7.

	A	B	C	F
1	Player	Team	Score	Sorted by score
2	Nadia	Red	1840
3	Oscar	Blue	2215
4	Hiro	Red	1975
5	Lucía	Green	2460
6	Felix	Blue	1620
7	Zara	Green	2090
8
9
10

Tip: SORT takes the table, then the column number to sort by, then -1 for descending.

Examples

=TEXTJOIN(", ",TRUE,SORT(A2:A7))→ "Desk speaker, Laptop stand, Noise-cancelling headset, USB-C hub, Webcam, Wireless mouse"

Sorts the product names A to Z. TEXTJOIN puts the spilled list in one cell so you can read it here.

=INDEX(SORT(A2:D7,4,-1),1,1)→ "USB-C hub"

Sorts the whole table by column 4, Units sold, highest first, and takes the first product: the best seller.

=TEXTJOIN(", ",TRUE,SORT(FILTER(A2:A7,B2:B7="Audio")))→ "Desk speaker, Noise-cancelling headset"

FILTER keeps only Audio products, then SORT puts them in alphabetical order.

Example data

1	Product	Category	Price	Units sold
2	Wireless mouse	Accessories	29	340
3	Noise-cancelling headset	Audio	149	120
4	USB-C hub	Accessories	45	410
5	Desk speaker	Audio	89	95
6	Laptop stand	Accessories	39	260
7	Webcam	Video	69	180

Common mistakes

#SPILL! error
SORT needs empty cells for its whole result. If anything sits in the spill area, clear it or move the formula somewhere with room.
Header row sorted into the data
Including row 1 in array sorts the header in with everything else. Start the range on the first data row and type headers above the formula.
Counting sort_index from column A
sort_index counts from the left edge of array, not the sheet. For SORT(B2:D7,2), column 2 is C.
Expecting the original data to change
SORT only writes a sorted copy. To reorder the source rows themselves, use the Sort button on the Data tab.

FAQ

How do I sort from largest to smallest with a formula?+

Set sort_order to -1: =SORT(A2:D7,4,-1) sorts by the fourth column, highest first.

How do I sort by two columns?+

Give arrays for the index and order: =SORT(A2:D7,{2,3},{1,-1}) sorts by column 2 A to Z, then by column 3 highest first. SORTBY is another option.

Which versions of Excel have SORT?+

Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions show #NAME?.

Lesson

Master SORT step by step: SORT: order data with a formula

Sort a table by any column, ascending or descending, and combine SORT with FILTER and UNIQUE.

Related functions

FILTER UNIQUE XLOOKUP