What it does
The Sort command on the Data tab rearranges the table itself. The SORT function produces a sorted copy instead, and the copy re-sorts whenever the source changes: update a sales figure and the order changes by itself.
The second argument says which column of the range to sort by, the third gives the direction: 1 ascending, -1 descending. With neither, the range is sorted by its first column in ascending order. If the fourth argument is TRUE, columns are sorted instead of rows.
To sort by a column that is not in the result, or by several columns, SORTBY is easier. Combine it with TAKE in Microsoft 365 to get the top N records; to sort only the rows that meet a condition, put FILTER inside SORT.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])=SIRALA(dizi; [sıralama_dizini]; [sıralama_düzeni]; [sütuna_göre])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range to sort, e.g. A2:C6. Leave out the header row. |
sort_index | Optional | The position of the column to sort by, inside the range; 1 if omitted. |
sort_order | Optional | 1 or omitted: ascending (A to Z, smallest to largest, oldest to newest). -1: descending. |
by_col | Optional | FALSE or omitted: sorts rows. TRUE: sorts columns. |
Examples
1Sort a table by amount, largest first
The sort uses the 3rd column of the range, the amount (3), in descending order (-1). The result spills from E2 across three columns and five rows.
=SORT(A2:C6,3,-1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Rep | Region | Amount | |
| 2 | Emma | West | 48,500 | Liam | North | 61,200 | |
| 3 | Liam | North | 61,200 | Ava | North | 57,800 | |
| 4 | Olivia | West | 52,300 | Olivia | West | 52,300 | |
| 5 | Noah | South | 29,750 | Emma | West | 48,500 | |
| 6 | Ava | North | 57,800 | Noah | South | 29,750 |
- E2 (the result spills into E2:G6)
=SORT(A2:C6,3,-1)
Liam (61,200) is at the top and Noah at the bottom. Raise Noah's amount in the source and the order changes by itself.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Sort open invoices by due date
FILTER first takes the invoices whose status is "Open"; SORT then orders them by the 2nd column, the due date, ascending. The invoice due soonest comes first.
=SORT(FILTER(A2:C7,D2:D7="Open"),2,1)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Due Date | Amount | Status | Invoice | Due Date | Amount | |
| 2 | F-2041 | 20-Nov-2026 | 12,400.00 | Open | F-2045 | 10-Nov-2026 | 2,600.00 | |
| 3 | F-2042 | 8-Nov-2026 | 3,850.00 | Paid | F-2043 | 12-Nov-2026 | 7,200.00 | |
| 4 | F-2043 | 12-Nov-2026 | 7,200.00 | Open | F-2041 | 20-Nov-2026 | 12,400.00 | |
| 5 | F-2044 | 1-Dec-2026 | 15,900.00 | Open | F-2044 | 1-Dec-2026 | 15,900.00 | |
| 6 | F-2045 | 10-Nov-2026 | 2,600.00 | Open | ||||
| 7 | F-2046 | 15-Nov-2026 | 9,100.00 | Paid |
- F2 (the result spills into F2:H5)
=SORT(FILTER(A2:C7,D2:D7="Open"),2,1)
The four open invoices are ordered by due date: F-2045 (10-Nov-2026) comes first and F-2044 last. Once an invoice is marked as paid, it drops off the list by itself.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #SPILL!
Cause: A cell in the spill area is not empty.
Fix: Clear the cells below and to the right of the formula.
- #VALUE!
Cause: sort_index is less than 1 or larger than the number of columns in the range.
Fix: Use a column position that falls inside the range.
- Unexpected order
Cause: Numbers are stored as text; compared as text, "1000" comes before "200".
Fix: Convert the column to real numbers.
Good to know
- Type the headers once above the result and give SORT only the data rows.
- To refer to the whole spilled result, add # to the first cell's address:
=COUNTA(E2#).
In Nexell
Get Nexell to do it from a sentence
Show the sales table sorted by amount from largest to smallest on a separate sheet
Convert to tableBring your table into the tool and type this. Nexell builds the sorted table and shows it in the preview first; once you approve, you download the file as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between SORT and SORTBY?
SORT sorts by the position of a column inside the range. SORTBY takes the sort key as a separate range, which does not have to appear in the result, and makes it easy to add more keys.
Related functions
- SORTBYSIRALAÖLÇÜT · Sorts a range by the values in another array.
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- UNIQUEBENZERSİZ · List of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.
- TAKEAL · Takes rows or columns from the start or end of an array.
- LARGEBÜYÜK · k-th largest value.