Excel SORT and SORTBY Functions

last modified October 5, 2026

The SORT and SORTBY functions are dynamic array functions in Excel that order data without touching the source. SORT rearranges the contents of a range or array in ascending or descending order, while SORTBY sorts a range based on the values of a separate range or array. Both return a new spilled array that updates automatically when the source data changes. This tutorial provides a comprehensive guide to using these functions with detailed examples. You'll learn basic syntax, single and multi-level sorting, horizontal data, legacy helper-column techniques, and troubleshooting for these essential Excel functions.

SORT and SORTBY Function Basics

The SORT function sorts a range or array by one of its own rows or columns. The SORTBY function sorts a range by any separate range or array, which does not need to be part of the source data or appear in the result. Both functions return dynamic arrays that spill into neighbouring cells.

Function	Description	Syntax
SORT	Sorts a range or array by its own row or column	=SORT(array,[sort_index],[sort_order],[by_col])
SORTBY	Sorts a range by another range or array	=SORTBY(array,by_array1,[sort_order1],...)

This table compares the two sorting functions. SORT takes a sort_index number, so the sort key must be a row or column of the array itself, and it accepts a single sort level. SORTBY takes by_array ranges, so it can sort by values that live elsewhere, are calculated on the fly, or should not appear in the output, and it supports several sort levels. SORTBY has no by_col argument: it sorts rows when by_array is vertical and columns when by_array is horizontal.

The table below describes each argument of SORT in more detail.

Argument	Meaning
array	Required. The range or array to sort. The sorted result has the same shape as this argument.
sort_index	Optional. A number indicating the row or column to sort by. The default is 1, which is the first column when sorting rows. A number outside the array's dimensions returns a #VALUE! error.
sort_order	Optional. 1 sorts ascending (the default) and -1 sorts descending.
by_col	Optional. FALSE (the default) sorts rows vertically; TRUE sorts columns horizontally.

The next table describes the arguments of SORTBY.

Argument	Meaning
array	Required. The range or array to sort.
by_array1	Required. A range or array to sort on. It must be exactly one row high or one column wide, and it must have one value per sorted row or column of array, otherwise the formula returns #VALUE!.
sort_order1	Optional. 1 sorts ascending (the default) and -1 sorts descending. Any other number returns a #VALUE! error.
by_array2, sort_order2, ...	Optional. Additional array and order pairs that add more sort levels. Each pair is applied after the previous one.

Both functions are available in Excel for Microsoft 365, Excel 2021, Excel 2024, and Excel for the web. In Excel 2019 and earlier they do not exist and produce a #NAME? error. Because the results are dynamic arrays, they need enough empty cells to spill into; otherwise Excel reports a #SPILL! error. Neither function rearranges the source range: the sorted output is a copy in a new location, and the original data stays where it is.

Basic SORT Example

This example demonstrates the simplest use of the SORT function with a single column of data.

A	B
Apple
Orange
Banana
	=SORT(A1:A3)

The table shows a simple list of fruits in column A and a SORT formula in cell B4 that sorts the list alphabetically in ascending order.

=SORT(A1:A3)

This formula sorts the values in cells A1 through A3 alphabetically. The result spills vertically into B4:B6 as Apple, Banana, Orange. With no optional arguments, SORT uses the first column of the array and ascending order, and it returns the values in the same shape as the source range.

SORT in Ascending and Descending Order

SORT sorts ascending unless told otherwise. The sort_order argument accepts 1 for ascending and -1 for descending, and it works with either an explicit or an omitted sort_index.

Formula	Result
=SORT(A1:A3)	Apple, Banana, Orange
=SORT(A1:A3,1,1)	Apple, Banana, Orange
=SORT(A1:A3,,-1)	Orange, Banana, Apple

The three formulas sort the fruit list from the previous example. The first two are identical, because sort_index 1 and sort_order 1 are the defaults; writing them out only documents the intent. The last formula leaves sort_index empty between two commas and passes -1, so the same list comes back in descending order. Text is compared case-insensitively from A to Z and numbers sort smallest to largest, so in an ascending sort numbers are placed before text.

SORT by a Specific Column

SORT can handle multi-column ranges, sorting by a specified column. This example shows sorting a table by the second column.

A	B	C
John	35
Mary	28
Bob	42
		=SORT(A1:B3,2,-1)

This table demonstrates sorting a two-column range by the second column (ages) in descending order. The sort_index is 2, and sort_order is -1 for descending.

=SORT(A1:B3,2,-1)

This formula sorts the range A1:B3 by the second column in descending order. Entire rows move together, so each name keeps its age: the result is Bob (42), John (35), Mary (28). The sort_index parameter determines which column to sort by, and sort_order -1 reverses the default ascending direction.

SORT with Horizontal Data

SORT can sort horizontal ranges by setting the by_col parameter. This example shows sorting a row of data.

A	B	C	D
	March	January	February
			=SORT(B1:D1,,,TRUE)

The table demonstrates sorting months horizontally. The by_col parameter is set to TRUE to sort columns instead of rows.

=SORT(B1:D1,,,TRUE)

This formula sorts the horizontal range B1:D1 alphabetically. The result spills into B2:D2 as February, January, March. With by_col set to TRUE, the sort_index counts rows instead of columns; here the default of 1 refers to the array's only row.

SORT Does Not Change the Source Data

Unlike the Sort command on the Data tab, which rearranges cells in place, SORT and SORTBY always write a sorted copy to a new location. The source range keeps its original order, and the spilled result recalculates whenever the source values change. This makes the functions safe for reports and dashboards: the underlying table can stay in its natural entry order while every view is sorted on demand.

The price of this behaviour is that the spill range must stay clear. If you later type a value into a cell the result wants to occupy, the formula returns a #SPILL! error until the blocking cell is removed. To rearrange data permanently, use the Data tab's Sort command instead of a formula.

SORTBY with Single Criteria

SORTBY sorts a range based on values in another range. This example shows basic usage with one sort criterion.

A	B	C
Red	3
Blue	1
Green	2
		=SORTBY(A1:A3,B1:B3)

The table shows colors in column A and numbers in column B. The SORTBY formula sorts column A based on the values in column B in ascending order.

=SORTBY(A1:A3,B1:B3)

This formula sorts the range A1:A3 based on values in B1:B3. The result is Blue, Green, Red, ordered by the numbers 1, 2, 3. The key range is not part of the array being sorted, so the sorted colors spill into C4:C6 without the numbers. SORTBY is useful whenever the sort key is separate from the data you want to display.

Multi-level Sorting with SORTBY

SORTBY can sort using multiple criteria with different sort orders. This example demonstrates two-level sorting.

A	B	C	D
Apple	Fruit	10
Carrot	Vegetable	15
Banana	Fruit	8
			=SORTBY(A1:C3,B1:B3,1,C1:C3,-1)

This table shows a three-column dataset. The SORTBY formula first sorts by category (ascending), then by price (descending) within each category.

=SORTBY(A1:C3,B1:B3,1,C1:C3,-1)

This formula sorts the range A1:C3 first by column B (category, ascending), then by column C (price, descending) within each category. The result is Apple (Fruit, 10), Banana (Fruit, 8), then Carrot (Vegetable, 15): Fruit comes before Vegetable alphabetically, and within the fruit group Apple precedes Banana because descending price puts the higher price first. Each additional by_array/sort_order pair adds another level that is applied after the previous ones.

SORTBY with Mixed Sort Directions

SORTBY allows specifying different sort directions for each criterion. This example shows mixed ascending and descending sorts.

A	B	C	D
North	200	Q1
South	150	Q2
East	200	Q2
			=SORTBY(A1:C3,B1:B3,-1,C1:C3,1)

The table demonstrates sorting first by sales (descending), then by quarter (ascending) for records with equal sales values. This creates a multi-level sort with different directions.

=SORTBY(A1:C3,B1:B3,-1,C1:C3,1)

This formula sorts the range A1:C3 first by column B (sales, descending), then by column C (quarter, ascending). The result is North (200, Q1), East (200, Q2), then South (150, Q2). North and East share the top sales figure, so the second key decides: Q1 comes before Q2 in ascending order. When rows tie on every key, SORTBY is a stable sort and keeps them in their original order.

SORTBY with Custom Order

SORTBY can implement custom sort orders using helper columns. This example shows sorting by priority levels.

A	B	C	D
Task 1	High	2
Task 2	Medium	1
Task 3	Low	3
			=SORTBY(A1:B3,C1:C3)

The table shows tasks with priority labels and numeric values. The SORTBY formula uses the numeric values to establish a custom sort order that doesn't follow alphabetical sequence.

=SORTBY(A1:B3,C1:C3)

This formula sorts the range A1:B3 based on values in C1:C3. The result shows the tasks in Medium (1), High (2), Low (3) order. This technique is useful for implementing non-alphabetical sort sequences. Without a helper column, MATCH can compute the positions directly: =SORTBY(A1:B3,MATCH(B1:B3,{"High","Medium","Low"},0)) returns the tasks in High, Medium, Low order.

Sorting Without Dynamic Arrays

In Excel 2019 and earlier there is no SORT or SORTBY. The classic alternative is a helper column that computes each row's position, combined with INDEX and MATCH to pull the rows back in that order. The example below reproduces the descending age sort of the earlier SORT example.

A	B	C	D
John	35	2	Bob
Mary	28	3	John
Bob	42	1	Mary

Column C ranks the ages from largest to smallest and column D lists the names in that order, which matches the result of =SORT(A1:B3,2,-1).

=RANK(B1,$B$1:$B$3)                                 ' helper in C1, copied down
=INDEX($A$1:$A$3,MATCH(ROWS($D$1:D1),$C$1:$C$3,0))  ' sorted list in D1, copied down

RANK numbers the ages from largest to smallest, so Bob gets 1, John 2, and Mary 3. The INDEX/MATCH formula in D1 looks up position 1, and the ROWS($D$1:D1) counter grows to 2 and 3 as the formula is copied down, returning Bob, John, and Mary. When values tie, RANK returns the same number for both rows; add a tiebreaker such as =RANK(B1,$B$1:$B$3)+COUNTIF($B$1:B1,B1)-1 so every row gets a unique position.

SORT with Dynamic Arrays

SORT can be combined with other dynamic array functions. This example shows sorting filtered results.

A	B	C
Desk	120
Lamp	45
Chair	210
		=SORT(FILTER(A1:B3,B1:B3>100),2,-1)

The table shows products with prices. The FILTER inside SORT first selects the expensive items and the outer SORT orders them.

=SORT(FILTER(A1:B3,B1:B3>100),2,-1)

The inner FILTER keeps the rows whose price exceeds 100, namely Desk (120) and Chair (210), and the outer SORT orders them by the second column in descending order, so Chair comes first. Because both functions return arrays, they can be nested freely to build complete data pipelines in a single formula.

SORT vs SORTBY

The two functions overlap, but each has a situation it handles best. The table below summarises the differences.

Feature	SORT	SORTBY
Sort key	A row or column of the array itself, chosen by index number	Any separate one-row or one-column range or array
Sort levels	A single sort_index, so one level	Many by_array/sort_order pairs for multi-level sorting
Horizontal data	Set by_col to TRUE	Automatic; a horizontal by_array sorts columns
Key hidden from output	Not possible	Natural fit; the key range is not part of array
Custom order	Alphabetical and numeric order only	Custom sequences via helper arrays such as MATCH
Column resilience	A fixed sort_index keeps pointing at the same position when columns are inserted	Range references follow their columns automatically

Use SORT for quick, single-level ordering of data that already contains its key. Reach for SORTBY when the key must be calculated, lives outside the data, should stay invisible, or when several sort levels are needed.

SORT with Error Handling

Both functions pass error values straight through: if the array or any by_array contains #N/A, #DIV/0!, or a similar error, the error appears in the sorted result. Wrapping the source range in IFERROR converts the errors before sorting.

=SORT(IFERROR(A1:B10,""),1,1)

This formula sorts the range A1:B10 after replacing every error value with an empty string. IFERROR works element by element on the array, so only the failing cells change. Empty strings are text, so they sort with the text values: in an ascending sort they are placed after any numbers and before the remaining text. To trap the errors instead of sorting around them, wrap the whole formula in IFERROR and supply a fallback value.

SORT and SORTBY Errors and Troubleshooting

Cause	Symptom	Fix
Formula used in Excel 2019 or earlier	#NAME? error	Use Excel 2021, Excel 2024, or Microsoft 365, or a helper-column approach
Spill range is blocked by other data	#SPILL! error	Clear the cells the result needs, or move the formula
sort_index is outside the array (SORT)	#VALUE! error	Use an index within the array's rows or columns
by_array is not one row or one column (SORTBY)	#VALUE! error	Make by_array a single row or a single column
by_array size differs from array (SORTBY)	#VALUE! error	Match the dimensions: one key value per sorted row or column
sort_order other than 1 or -1 (SORTBY)	#VALUE! error	Use 1 for ascending or -1 for descending
Source data contains error values	The errors pass into the result	Clean the data or wrap the range in IFERROR before sorting
Source workbook is closed	#REF! error when refreshed	Keep both workbooks open for cross-workbook dynamic arrays

The SORT and SORTBY functions turn static lists into live, self-updating sorted views. SORT is the quick choice when the key is one of the array's own columns or rows, while SORTBY handles separate keys, calculated keys, custom orders, and many sort levels. Neither touches the source data, and both spill results that resize and recalculate automatically. Remember that they require Excel 2021 or Microsoft 365, that a blocked spill range raises #SPILL!, and that mismatched by_array dimensions raise #VALUE!. Mastering both functions will make your spreadsheets shorter, clearer, and far easier to maintain.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.