Dynamic arrays
How to Use SORT in Excel (Live Sorting, with Examples)
The SORT function arranges a range in order and spills the sorted result into new cells, leaving your original data untouched. Unlike the Sort button on the ribbon, it recalculates automatically, so the output stays sorted as the source data changes.
SORT is a dynamic-array function, available in Excel 365, Excel 2021 and later, and Google Sheets. This guide covers the syntax, sorting by any column ascending or descending, and its companion SORTBY for sorting one range by the values in another.
SORT syntax
=SORT(array, [sort_index], [sort_order], [by_col]) | Argument | Required | What it does |
|---|---|---|
array | Required | The range to sort, such as A2:C100. The sorted copy spills into new cells. |
sort_index | Optional | Which column (or row) to sort by, counting from 1. Defaults to 1, the first column. |
sort_order | Optional | 1 for ascending (default) or -1 for descending. |
by_col | Optional | FALSE (default) sorts rows top to bottom; TRUE sorts columns left to right. |
Note: SORT spills, so leave room below and to the right. Use SORTBY when you want to sort by a column you are not returning.
Examples
Real, copy-paste-ready formulas.
1. Sort a table by a column descending
=SORT(A2:C100, 3, -1) Sorts every row by the third column in descending order (-1). The whole table spills out sorted, while the source range stays as it was.
2. Sort a single list A to Z
=SORT(A2:A100) With the optional arguments left out, SORT defaults to the first column, ascending order. This is the quickest way to alphabetize a list.
3. Sort by a column you do not return
=SORTBY(A2:A100, C2:C100, -1) SORTBY sorts one range by another. Here the names come back ordered by score descending, even though the score column is not part of the output.
4. Sort by two columns at once
=SORTBY(A2:C100, B2:B100, 1, C2:C100, -1) SORTBY accepts multiple by-arrays and orders in pairs: first region ascending, then amount descending within each region.
How to write SORT step by step
- 1
Click the top-left cell where you want the sorted result and type =SORT(
- 2
Select the range you want to sort (the array), then type a comma.
- 3
Type the column number to sort by (1, 2, 3...), then a comma.
- 4
Type 1 for ascending or -1 for descending order.
- 5
Close the bracket ) and press Enter. For sorting by a separate column, use SORTBY instead.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#SPILL! | Something is blocking the cells where the sorted result needs to appear. | Clear the cells below and to the right of the formula, or move it to an empty area. |
#VALUE! | The sort_index points to a column that does not exist in the array, or by-arrays in SORTBY are different sizes. | Use a sort_index within the array, and make every SORTBY by-array the same height as the data. |
#NAME? | SORT or SORTBY is not available in this version of Excel. | These need Excel 365 or Excel 2021. In older versions, use the Sort button on the Data tab instead. |
#CALC! | The array argument is empty or resolves to no data. | Point SORT at a range that actually contains values. |
Frequently asked questions
What is the difference between SORT and SORTBY?
SORT orders a range by one of its own columns, chosen by number. SORTBY orders a range by the values in one or more separate ranges, which can be columns you are not returning. SORTBY is also the easier choice for multi-level sorts.
How do I sort in descending order?
Set the sort_order argument to -1, for example =SORT(A2:C100, 3, -1). A value of 1 (or leaving it out) sorts ascending.
Does SORT change my original data?
No. SORT returns a sorted copy in new cells and leaves the source range exactly as it is. To sort in place permanently, use the Sort button on the Data tab.
Does SORT work in Google Sheets?
Yes. Google Sheets has both SORT and SORTBY. The SORT argument order differs slightly, using a column number and a TRUE/FALSE for ascending, but the idea is the same.