Excel’s SORT function arranges a range in a chosen order and updates the results when the source data changes.
These examples show how to sort numbers, text, and multi-column tables, including how to choose the sort column and avoid common errors.
Excel SORT function syntax
SORT returns a sorted copy of a range as a dynamic array.
The source cells stay in place; the result spills into neighboring cells.
=SORT(array,[sort_index],[sort_order],[by_col])
arrayis the range or array to sort.sort_indexis the row or column number to sort by.
It defaults to 1.sort_orderis 1 for ascending or -1 for descending.
It defaults to 1.by_colis FALSE or omitted to sort rows; TRUE sorts columns.
Square brackets mark optional arguments; do not type them into the formula.
For example, a formula can specify just a range and use the default ascending order.
Example data for the formulas
Suppose cells A1:B5 contain this table, with headers in row 1:
| Product | Sales |
|---|---|
| Notebook | 42 |
| Pen | 18 |
| Binder | 31 |
| Folder | 25 |
For formulas that should include the headers, use A1:B5.
For formulas that sort only the records, use A2:B5.
Put the formula somewhere with enough empty cells below and to the right for the returned array.
Sort numbers from smallest to largest
To sort the sales values in B2:B5 in ascending order, enter:
=SORT(B2:B5)
The result is 18, 25, 31, and 42.
The default sort order is ascending, so the shorter version works.
To make the direction explicit, use:
=SORT(B2:B5,1,1)
Here, the second argument identifies the first column in the one-column array, and the third sets ascending order.
Sort numbers from largest to smallest
To show the highest sales first, use:
=SORT(B2:B5,1,-1)
The result is 42, 31, 25, and 18.
The value -1 requests descending order; 1 requests ascending order.
This is useful for ranking a list by a single numeric measure, though SORT does not add rank numbers.
Sort a table by one column
To return the products and sales together, ordered by sales from highest to lowest, use:
=SORT(A2:B5,2,-1)
The sort index 2 refers to the second column within A2:B5, which is Sales.
The expected rows are Notebook (42), Binder (31), Folder (25), and Pen (18).
Sorting the entire two-column range keeps each product matched with its sales value.
To sort the same table alphabetically by product, change the sort index to 1:
=SORT(A2:B5,1,1)
The expected order is Binder, Folder, Notebook, and Pen, with each sales value remaining on its corresponding row.
Keep headers above a sorted result
SORT treats every row in its array as data.
If you include A1:B5, it will sort the header row along with the product records.
A straightforward approach is to put the headers in the output area manually and sort only A2:B5 underneath them:
=SORT(A2:B5,2,-1)
Another option in Microsoft 365 is to combine the header row and sorted records with VSTACK:
=VSTACK(A1:B1,SORT(A2:B5,2,-1))
VSTACK is a separate dynamic-array function, so its availability depends on the Excel version and update channel.
If it is unavailable, use the separate-header approach.
Sort data across columns
By default, SORT orders rows.
To sort columns instead, set by_col to TRUE.
For example, if A1:D2 contains two rows of values and each column represents a month, this formula sorts the columns according to the values in the first row:
=SORT(A1:D2,1,1,TRUE)
The sort index 1 identifies the first row when sorting by columns.
Use this less common option when each column is a record or time period and the layout must remain horizontal.
Use SORT with a separate sort range
If you want to sort one range using values in another range, use SORTBY.
For example, sort product names in A2:A5 by their matching sales values in B2:B5, highest first:
=SORTBY(A2:A5,B2:B5,-1)
SORTBY is often easier to read when the sort key is outside the array or when you need multiple sort keys.
To sort by sales descending and then product name ascending, use:
=SORTBY(A2:B5,B2:B5,-1,A2:A5,1)
Common SORT errors and edge cases
- Spill range is blocked: A value, merged cell, or other obstruction in the output area can prevent the result from spilling.
Clear the cells where the sorted array needs to appear. - Headers appear among the records: Sort the data rows without the header, then add headers separately or use VSTACK where supported.
- Sort index is out of range: The index is relative to the array, not the worksheet column.
For A2:B5, the valid column indexes are 1 and 2. - Unexpected text order: Numbers stored as text may sort differently from numeric values.
Check the source data’s number format and underlying values. - Duplicate sort values: Rows with equal sort keys can appear in either order.
Add a secondary key with SORTBY when you need a defined tie-breaker. - Blank or changing source ranges: A fixed reference such as A2:B1000 may include blank rows.
Consider using an Excel Table reference so the source expands with new records. - Formula shows #NAME?: Your Excel version may not support SORT, or the function name and list separators may differ in your language or regional settings.
Excel versions that support SORT
SORT is a dynamic-array function available in Microsoft 365 and newer perpetual Excel versions that include dynamic arrays, such as Excel 2021 and Excel 2024.
It is not available in older releases such as Excel 2019 and Excel 2016.
Excel for the web also supports dynamic-array formulas, though feature availability can vary by update and account.
In a version without SORT, use the Data tab’s Sort command for a one-time sort, or use older formulas and helper columns for a formula-driven result.
When opening a workbook in an older version, dynamic-array formulas may not calculate as expected.
Check compatibility before sharing a workbook with users on older Excel editions.