Skip to content

Excel function · Lookup & reference

Excel SORT function

SORT returns a range sorted by the column you choose, smallest to largest or the other way round, in another place; the source table stays as it is. It is available in Microsoft 365 and Excel 2021.

In Turkish Excel: SIRALA

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

English Excel=SORT(array, [sort_index], [sort_order], [by_col])
Turkish Excel=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

ArgumentRequiredDescription
arrayRequiredThe range to sort, e.g. A2:C6. Leave out the header row.
sort_indexOptionalThe position of the column to sort by, inside the range; 1 if omitted.
sort_orderOptional1 or omitted: ascending (A to Z, smallest to largest, oldest to newest). -1: descending.
by_colOptionalFALSE 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.

E2fx=SORT(A2:C6,3,-1)
Sort a table by amount, largest first: example table
ABCDEFG
1RepRegionAmountRepRegionAmount
2EmmaWest48,500LiamNorth61,200
3LiamNorth61,200AvaNorth57,800
4OliviaWest52,300OliviaWest52,300
5NoahSouth29,750EmmaWest48,500
6AvaNorth57,800NoahSouth29,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.

F2fx=SORT(FILTER(A2:C7,D2:D7="Open"),2,1)
Sort open invoices by due date: example table
ABCDEFGH
1InvoiceDue DateAmountStatusInvoiceDue DateAmount
2F-204120-Nov-202612,400.00OpenF-204510-Nov-20262,600.00
3F-20428-Nov-20263,850.00PaidF-204312-Nov-20267,200.00
4F-204312-Nov-20267,200.00OpenF-204120-Nov-202612,400.00
5F-20441-Dec-202615,900.00OpenF-20441-Dec-202615,900.00
6F-204510-Nov-20262,600.00Open
7F-204615-Nov-20269,100.00Paid
  • 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

Type this in Nexell

Show the sales table sorted by amount from largest to smallest on a separate sheet

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free