Excel SORT and SORTBY Functions: Sort with a Formula

=SORT(A2:C7,3,-1) returns the table A2:C7 sorted by its third column, sales, from largest to smallest. The original table stays as it is; the sorted copy spills from the formula cell and re-sorts itself when a number changes. Change C7 to 300 and Finn moves to the top.

FormulasReset

E2fx

=SORT(A2:C7,3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Dan	East	150
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Eve	South	95
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Finn	North	60

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

SORT syntax

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

• array is the range to sort and return.
• sort_index is the column number inside array to sort by. The default is 1.
• sort_order is 1 for ascending (the default) or -1 for descending.
• by_col is FALSE (the default) to sort rows, TRUE to sort columns left to right.

SORT and SORTBY need Excel 2021, Excel 2024 or Microsoft 365; older versions show #NAME?. Google Sheets has both, though its SORT takes a column number and TRUE or FALSE in pairs.

Ascending order puts numbers first, then text A to Z, then FALSE and TRUE. Text is compared without regard to case. Empty cells in array come back as 0 in the result.

How to alphabetize in Excel

To reorder a list in place, click any cell in it and choose Data > Sort A to Z (or Z to A). If the list has neighbouring columns, Excel sorts the whole table so each row stays together. Use Data > Sort for more than one column or a custom list.

To keep the original order and get an alphabetical copy that updates by itself, use SORT with no other arguments:

FormulasReset

C2fx

=SORT(A2:A8)

	A	B	C
1	Name		A to Z
2	Mia		Ann
3	Ben		Ben
4	Zoe		Cara
5	Ann		Dan
6	Leo		Leo
7	Cara		Mia
8	Dan		Zoe

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

Type Abe in A3 and he appears first. =SORT(A2:A8,,-1) gives Z to A; the two commas skip sort_index. To alphabetize a list without repeats, sort the result of UNIQUE: =SORT(UNIQUE(A2:A8)) (see UNIQUE).

SORTBY: sort by a column you do not return

SORT can only sort by a column of the range it returns. SORTBY takes the sort key as a separate range, so you can return names alone, ordered by sales:

FormulasReset

E2fx

=SORTBY(A2:A7,C2:C7,-1)

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120		Cara
3	Ben	South	80		Dan
4	Cara	North	200		Ann
5	Dan	East	150		Eve
6	Eve	South	95		Ben
7	Finn	North	60		Finn

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

The result is Cara, Dan, Ann, Eve, Ben, Finn. The syntax is =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...). Each by_array must have as many rows as array, otherwise SORTBY returns #VALUE!.

For a top 3, take the first rows of the result: =TAKE(SORTBY(A2:A7,C2:C7,-1),3) returns Cara, Dan and Ann. TAKE needs Microsoft 365 or Excel 2024; in Excel 2021 use =INDEX(SORTBY(A2:A7,C2:C7,-1),SEQUENCE(3)).

Sort by multiple columns

Give SORTBY one pair of key and order per level. This sorts by region A to Z, then within each region by sales from highest to lowest:

FormulasReset

E2fx

=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Dan	East	150
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Finn	North	60
6	Eve	South	95		Eve	South	95
7	Finn	North	60		Ben	South	80

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

East comes first, then the three North rows as Cara (200), Ann (120) and Finn (60), then South. SORT can do the same with arrays of column numbers and orders, though SORTBY is easier to read:

=SORT(A2:C7,{2,3},{1,-1})

Sort in a custom order

Alphabetical is not always the order you want. To sort regions as North, East, South, turn each region into its position in that list with MATCH and sort by the positions:

FormulasReset

E2fx

=SORTBY(A2:C7,MATCH(B2:B7,{"North","East","South"},0))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150		Dan	East	150
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Eve	South	95

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

MATCH returns 1 for North, 2 for East and 3 for South, and SORTBY sorts by those numbers. Rows with the same key keep their original order, so the North rows stay Ann, Cara, Finn. The same trick sorts months or weekdays by name: list them in calendar order inside MATCH. A region missing from the list gets #N/A from MATCH; use IFERROR(MATCH(B2:B7,{"North","East","South"},0),99) as the key to put such rows last.

Practice: names ordered by sales

FormulasReset

E2fx

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Click a cell to see its formula. Change a number or a formula and the sheet recalculates.

Your turn: In E2, return the names only, ordered by sales from highest to lowest.

CheckShow answer

SORT function or the Sort button

Both sort, but they do different jobs:

Use the button to tidy a table once. Use the function for a report or dashboard that has to stay sorted while the data changes. If the cells below or to the right of the formula are not empty, the function shows #SPILL! instead of the sorted list; clear them and it appears (see #SPILL! error).