Excel SORT Function: Order a Range With a Formula
Returns a range in sorted order, as a live result that re-sorts itself.
SORT does what the ribbon's sort button does, except as a formula, which changes what it is good for. The button rearranges your data permanently and has to be pressed again every time the data changes. SORT leaves the source untouched and produces a separate sorted copy that reorders itself the moment a value changes or a row is added.
That makes it the right tool for anything that has to stay current — a top-ten table on a dashboard, a leaderboard, an ordered summary that someone else will look at tomorrow. The source stays in whatever order it arrived in, which is usually the order you want for auditing it.
Its companion SORTBY sorts one range using the values in another, which is the version you want when the column you are sorting on is not the column you want to display.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])Arguments
arrayRequired- The range to sort. One column or many; the shape comes back unchanged.
sort_indexOptional- Which column of array to sort on, counting from 1 at its left edge. Defaults to 1.
sort_orderOptional- 1 for ascending (the default), -1 for descending.
by_colOptional- FALSE (the default) sorts rows. TRUE sorts columns left to right, for horizontally laid-out data.
The example data
Headers in row 1, data in A2:C6.
| A | B | C | |
|---|---|---|---|
| 1 | Player | Score | |
| 2 | Alice Moreau | 1240 | alice@northwind.com |
| 3 | Bruno Santos | 385 | bruno@southgate.co.uk |
| 4 | Chen Wei | 2100 | chen@northwind.com |
| 5 | Dana Okafor | 940 | dana@eastvale.org |
| 6 | Erik Halls | 1560 | erik@southgate.co.uk |
Worked examples
=SORT(A2:B6, 2, -1)Result: Chen Wei 2100, Erik Halls 1560, Alice Moreau 1240, Dana Okafor 940, Bruno Santos 385
Sorted on the second column, descending — the standard leaderboard formula.
=SORT(A2:A6)Result: Alice Moreau, Bruno Santos, Chen Wei, Dana Okafor, Erik Halls
A single column with no other arguments: sorted ascending by itself.
=SORTBY(A2:A6, B2:B6, -1)Result: Chen Wei, Erik Halls, Alice Moreau, Dana Okafor, Bruno Santos
Names ordered by score without the score column coming along. SORTBY takes the sort key as a separate range.
=SORT(FILTER(A2:B6, B2:B6>900), 2, -1)Result: Chen Wei 2100, Erik Halls 1560, Alice Moreau 1240, Dana Okafor 940
The combination worth learning: filter first, then sort what survives.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#SPILL!
Why it happens: The cells the sorted result needs are not empty.
How to fix it: Clear the block below and to the right. Remember it needs as many rows as the source has.
#VALUE!
Why it happens: sort_index is larger than the number of columns in array — sorting on column 3 of a two-column range.
How to fix it: Count columns from the left edge of array, not from column A of the sheet.
Numbers sort in the wrong order
Why it happens: They are stored as text, so they sort alphabetically: 100 lands before 20.
How to fix it: Convert them to real numbers. Text numbers align left, which is the quickest check.
#NAME?
Why it happens: The Excel version predates dynamic arrays.
How to fix it: SORT needs Microsoft 365 or Excel 2021+. Older versions need the ribbon's sort or a LARGE/INDEX construction.
Tips worth knowing
- SORTBY can take several key ranges: =SORTBY(A2:A6, C2:C6, 1, B2:B6, -1) sorts by email then by score.
- Wrap in TAKE for a top-N table: =TAKE(SORT(A2:B6, 2, -1), 3) gives the top three.
- SORT ignores the source's own formatting — the result is values, so style the destination separately.
- Reference the spilled result elsewhere with # : E2# means everything E2 produced.
Frequently asked questions
What is the difference between SORT and SORTBY?
SORT orders a range using a column inside that same range, named by position. SORTBY orders a range using values from a completely separate range, so you can sort names by a score column without displaying the scores. SORTBY also accepts multiple sort keys.
How do I show only the top five?
Sort descending and take the first five: =TAKE(SORT(A2:B100, 2, -1), 5). Without TAKE, wrap the SORT in INDEX or simply let it spill and read the first rows.
Does SORT change my original data?
No, and that is the main difference from the ribbon's sort button. The source stays exactly as it was; SORT produces a separate result that updates whenever the source changes.
Related functions
- FILTERReturns every row of a range that meets your condition, as a live result that resizes itself.
- UNIQUEReturns the distinct values from a range, with duplicates removed.
- INDEX MATCHMATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.
Guides that use it
Longer reads where this function does real work in a real sheet.