SORT and SORTBY in Excel: sort a range with a formula

SORT syntax and arguments

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

• array: the range or array to sort, one column or a whole table.
• sort_index (optional): which column of array to sort by, counted from 1. The default is 1, the first column.
• sort_order (optional): 1 ascending (A to Z, smallest first, the default) or -1 descending.
• by_col (optional): FALSE sorts rows (the default), TRUE sorts columns, for data laid out across a row.

The result is a dynamic array: you enter the formula in one cell and it spills into as many rows and columns as the array has. The source data stays where it is, which is the main difference to the Sort button on the Data tab. SORT and SORTBY are available in Microsoft 365, Excel 2021 and later and Excel for the web; older versions show #NAME?.

Step by step: sort a table by revenue

The table in A2:D9 has four columns: product, category, units and revenue. To sort it by revenue, largest first, the sort_index is 4 because revenue is the 4th column of the array, and the sort_order is -1:

=SORT(A2:D9,4,-1)

Excel spills eight rows and four columns. Echo Kettle (2,925) comes first, then Dune Lamp (2,250) and Cedar Board (2,000). The column number counts inside array, not inside the worksheet: if the array started in column B, revenue would be sort_index 3. To sort the names alone, Z to A, use =SORT(A2:A9,1,-1); the last entry is Alpine Mug.

A sorted list is often only the first step. =TAKE(SORT(D2:D9,1,-1),3) keeps the three largest values and =SUM(TAKE(SORT(D2:D9,1,-1),3)) adds them up: 2,925 + 2,250 + 2,000 = 7,175. For a top-N list with names, wrap the whole table: =TAKE(SORT(A2:D9,4,-1),3).

SORTBY syntax: sort by ranges, not column numbers

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)

Instead of a column number, each sort key is a range or array with the same height as array, followed by its order. That brings three advantages. The key does not have to be part of the result: =SORTBY(A2:A9,D2:D9,-1) returns only product names, sorted by revenue. The key can be a calculation: =SORTBY(A2:A9,D2:D9/C2:C9,-1) sorts by revenue per unit, so Dune Lamp (2,250 / 30 = 75.00) comes first without a helper column. And inserting a column into the source does not break the formula, because there is no column number that could shift.

Several keys are applied from left to right. =SORTBY(A2:A9,B2:B9,1,D2:D9,-1) sorts by category first and uses revenue only to order products inside the same category: Bath comes first with Glen Towel (1,350) before Haze Soap (840), then Home with Dune Lamp. SORT can do the same with array constants, =SORT(A2:D9,{2,4},{1,-1}), but only while the keys are columns of the result.

Worked example: eight products sorted with SORT and SORTBY

Eight fictional products with units and revenue (yellow inputs). Column E is a simple helper for comparison; the SORTBY formula in row 22 calculates revenue per unit itself. Hover over a green cell to see its formula.

Worked example: eight products sorted with SORT and SORTBY

	A	B	C	D	E
1	Product	Category	Units	Revenue	Revenue / unit
2	Alpine Mug	Kitchen	120	1,440	12.00
3	Birch Tray	Home	45	1,575	35.00
4	Cedar Board	Kitchen	80	2,000	25.00
5	Dune Lamp	Home	30	2,250	75.00
6	Echo Kettle	Kitchen	65	2,925	45.00
7	Fjord Vase	Home	45	900	20.00
8	Glen Towel	Bath	150	1,350	9.00
9	Haze Soap	Bath	210	840	4.00
10
11	SORT: the whole table by revenue, largest first (spilled rows 1 to 3)				Result
12	1st product: =SORT(A2:D9,4,-1), row 1, column 1				Echo Kettle
13	2nd product				Dune Lamp
14	3rd product				Cedar Board
15	Revenue of the top 3: SUM(TAKE(SORT(…),3))				7,175
16	Last product in Z to A order: SORT(A2:A9,1,-1), row 8				Alpine Mug
17	SORTBY: sort keys that are not part of the result				Result
18	Category A to Z, then revenue largest first: 1st				Glen Towel
19	Same order: 2nd				Haze Soap
20	Same order: 3rd				Dune Lamp
21	Same with SORT and two keys: SORT(A2:D9,{2,4},{1,-1})				Glen Towel
22	Highest revenue per unit, calculated inside SORTBY				Dune Lamp
23	Units largest first, tie broken by revenue: 6th				Birch Tray
24	Units largest first, tie broken by revenue: 7th				Fjord Vase
25	Combined with UNIQUE and FILTER				Result
26	First entry of SORT(UNIQUE(B2:B9))				Bath
27	Number of categories				3
28	Best seller in Kitchen: SORT(FILTER(…),4,-1)				Echo Kettle

Fictional products and figures. In Excel, one SORT or SORTBY formula spills the whole sorted list; the grid uses INDEX(…,k) so that each value can be shown and checked in its own cell.

Ties, text and blanks

Birch Tray and Fjord Vase both sold 45 units. Sorted by units alone, their order is simply the order of the source. If the order matters, add a tie-breaker as a second key: =SORTBY(A2:A9,C2:C9,-1,D2:D9,-1) puts Birch Tray (revenue 1,575) in 6th place and Fjord Vase (900) in 7th. A unique tie-breaker such as an ID or a date makes the result fully predictable.

Text sorts alphabetically and ignores upper and lower case. Numbers stored as text sort as text, so "100" can land before "20"; convert them with VALUE first. Empty rows inside the array are sorted along with the data and show up as 0 in the result. Sort only the filled rows by combining SORT with FILTER, for example =SORT(FILTER(A2:D50,A2:A50<>""),4,-1).

SORT with UNIQUE and FILTER, and when to use which

SORT is the usual outer layer of other dynamic array functions. =SORT(UNIQUE(B2:B9)) returns the three categories as a sorted list (Bath, Home, Kitchen), a good source for a drop-down list. =SORT(FILTER(A2:D9,B2:B9="Kitchen"),4,-1) returns only kitchen products, best seller first: Echo Kettle. See the guide on FILTER with multiple criteria for the include argument.

Use SORT when the sort key is a column of the result and you want a short formula. Use SORTBY when the key is not shown, is calculated, or when the table layout may change. Use the Sort button when the data itself should be rearranged once, for example before printing. For a ranking number next to each row instead of a reordered list, RANK.EQ is the better tool.

How the example shows the spilled result

In Excel, a single =SORT(A2:D9,4,-1) fills a block of 8 by 4 cells. The example grid on this page shows each checked value in its own cell, so it uses =INDEX(SORT(…),row,column) to pick one row of the same sorted array. The values are identical to what the spill range would show in that row.

Common mistakes

• Counting sort_index in the worksheet. The column number counts inside the array. In SORT(B2:D9,3,-1) the 3rd column is D, not C.
• Keys of a different size. Every by_array in SORTBY must have exactly as many rows as the array, otherwise the formula returns #VALUE!. Mixing D2:D9 with A2:A10 is a common cause.
• Cells in the way of the spill. Any value inside the area the result needs shows #SPILL!. Leave enough empty rows below the formula for the largest possible list.
• Forgetting the order argument. SORT and SORTBY sort ascending by default. For "largest first" you need -1, and in SORTBY you need it for each key separately.
• Expecting the source to change. SORT returns a sorted copy. The original table keeps its order, and formulas that point to the original rows are not affected.
• Sorting whole columns. SORT(A:D,4,-1) includes the header and a million empty rows. Use the exact range, a table reference or FILTER to drop empty rows.