The SORT function in Excel

SORT sorts with a formula instead of the Sort button. The original data stays where it is, and the sorted copy spills into the cells below and updates whenever the data changes.

Available in Excel 365 and Excel 2021 or newer.

Syntax

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

Argument	What it means
array	The range to sort.
sort_indexoptional	Which column of the range to sort by (1 = first). Default 1.
sort_orderoptional	1 = ascending (default), -1 = descending.
by_coloptional	TRUE to sort columns instead of rows. Rarely needed.

Examples

1. Largest first

F2fx=SORT(C2:C6,1,-1)

	A	B	C	D	F
1	Order	Region	Amount	Status	Amounts
2	1001	North	250	Paid	420
3	1002	South	90	Paid	250
4	1003	North	420	Open	130
5	1004	East	130	Paid	90
6	1005	North	75	Paid	75

-1 sorts descending. The five amounts spill into F2:F6.

2. Text in alphabetical order

F2fx=SORT(B2:B6)

	A	B	C	D	F
1	Order	Region	Amount	Status	Regions A–Z
2	1001	North	250	Paid	East
3	1002	South	90	Paid	North
4	1003	North	420	Open	North
5	1004	East	130	Paid	North
6	1005	North	75	Paid	South

With only the range, SORT sorts ascending, A to Z.

3. Each value once, sorted

F2fx=SORT(UNIQUE(B2:B6))

	A	B	C	D	F
1	Order	Region	Amount	Status	Region list
2	1001	North	250	Paid	East
3	1002	South	90	Paid	North
4	1003	North	420	Open	South
5	1004	East	130	Paid
6	1005	North	75	Paid

UNIQUE removes the duplicates, SORT puts them in order: a tidy list for a drop-down.

Common mistakes

#SPILL! error. Cells below the formula aren't empty.
Fix: Clear the space the sorted list needs.
Sorting one column of a table on its own. It no longer lines up with the other columns.
Fix: Sort the whole table and pick the column with sort_index: =SORT(A2:D6,3,-1) sorts all orders by amount.

Frequently asked questions

How do I sort by a column that I don't want to show?

Use SORTBY: =SORTBY(A2:A6,C2:C6,-1) returns the order numbers sorted by amount.

Related functions

Argument

What it means

array

The range to sort.

sort_indexoptional

Which column of the range to sort by (1 = first). Default 1.

sort_orderoptional

1 = ascending (default), -1 = descending.

by_coloptional

TRUE to sort columns instead of rows. Rarely needed.

Examples

1. Largest first

F2fx=SORT(C2:C6,1,-1)

	A	B	C	D	F
1	Order	Region	Amount	Status	Amounts
2	1001	North	250	Paid	420
3	1002	South	90	Paid	250
4	1003	North	420	Open	130
5	1004	East	130	Paid	90
6	1005	North	75	Paid	75

-1 sorts descending. The five amounts spill into F2:F6.

2. Text in alphabetical order

F2fx=SORT(B2:B6)

	A	B	C	D	F
1	Order	Region	Amount	Status	Regions A–Z
2	1001	North	250	Paid	East
3	1002	South	90	Paid	North
4	1003	North	420	Open	North
5	1004	East	130	Paid	North
6	1005	North	75	Paid	South

With only the range, SORT sorts ascending, A to Z.

3. Each value once, sorted

F2fx=SORT(UNIQUE(B2:B6))

	A	B	C	D	F
1	Order	Region	Amount	Status	Region list
2	1001	North	250	Paid	East
3	1002	South	90	Paid	North
4	1003	North	420	Open	South
5	1004	East	130	Paid
6	1005	North	75	Paid

UNIQUE removes the duplicates, SORT puts them in order: a tidy list for a drop-down.