Excel SORT Function
SORT Function
The SORT function is a premade function in Excel, which returns a sorted copy of a range.
The sorted copy is placed where you type the formula. The original data is not changed.
This page is about the SORT function, which you type in a cell. To sort the data itself with the Sort commands in the ribbon, see the Excel Sort page.
It is typed =SORT and has the following parts:
=SORT(array, [sort_index], [sort_order], [by_col])
Note: SORT is available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019 or earlier. If you type it in an older version, you get a #NAME? error.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Array: The range to sort. Leave out the header row.
Sort_index: Optional. The number of the column to sort by, counted from the left of the array. The default is 1, the first column.
Sort_order: Optional. The order to sort in:
| Sort_order | Description |
|---|---|
1 | Ascending, from smallest to largest (A to Z). This is the default. |
-1 | Descending, from largest to smallest (Z to A). |
By_col: Optional. FALSE sorts rows, which is the default. TRUE sorts columns, for data that goes across instead of down. You will rarely need it.
How to use the SORT function:
- Select a cell (
G2) - Type
=SORT - Double click the SORT command
- Mark the range to sort (
A2:E21) - Type (
,) - Type the number of the column to sort by (
5) - Type (
,) - Type the sort order (
-1) - Hit enter
Let's have a look at an example!
This is the same Pokemon table as on the XLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
SORT does not return the header row. Copy the headers in A1:E1 and paste them into G1.
Use the SORT function to sort the Pokemon by Total, from largest to smallest. Type the formula in G2:
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | ID# | Name | Type 1 | Type 2 | Total | |
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | 6 | Charizard | Fire | Flying | 534 | |
| 3 | 2 | Ivysaur | Grass | Poison | 405 | 9 | Blastoise | Water | 0 | 530 | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | 3 | Venusaur | Grass | Poison | 525 | |
| 5 | 4 | Charmander | Fire | 309 | 18 | Pidgeot | Normal | Flying | 479 | ||
| 6 | 5 | Charmeleon | Fire | 405 | 20 | Raticate | Normal | 0 | 413 | ||
| 7 | 6 | Charizard | Fire | Flying | 534 | 2 | Ivysaur | Grass | Poison | 405 | |
| 8 | 7 | Squirtle | Water | 314 | 5 | Charmeleon | Fire | 0 | 405 | ||
| 9 | 8 | Wartortle | Water | 405 | 8 | Wartortle | Water | 0 | 405 | ||
| 10 | 9 | Blastoise | Water | 530 | 12 | Butterfree | Bug | Flying | 395 | ||
| 11 | 10 | Caterpie | Bug | 195 | 15 | Beedrill | Bug | Poison | 395 | ||
| 12 | 11 | Metapod | Bug | 205 | 17 | Pidgeotto | Normal | Flying | 349 | ||
| 13 | 12 | Butterfree | Bug | Flying | 395 | 1 | Bulbasaur | Grass | Poison | 318 | |
| 14 | 13 | Weedle | Bug | Poison | 195 | 7 | Squirtle | Water | 0 | 314 | |
| 15 | 14 | Kakuna | Bug | Poison | 205 | 4 | Charmander | Fire | 0 | 309 | |
| 16 | 15 | Beedrill | Bug | Poison | 395 | 19 | Rattata | Normal | 0 | 253 | |
| 17 | 16 | Pidgey | Normal | Flying | 251 | 16 | Pidgey | Normal | Flying | 251 | |
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | 11 | Metapod | Bug | 0 | 205 | |
| 19 | 18 | Pidgeot | Normal | Flying | 479 | 14 | Kakuna | Bug | Poison | 205 | |
| 20 | 19 | Rattata | Normal | 253 | 10 | Caterpie | Bug | 0 | 195 | ||
| 21 | 20 | Raticate | Normal | 413 | 13 | Weedle | Bug | Poison | 195 |
=SORT(A2:E21, 5, -1)
Total is the 5th column of A2:E21, so sort_index is 5. Sort_order -1 sorts from largest to smallest.
Charizard has the highest Total (534). Caterpie and Weedle have the lowest (195).
The formula is typed only in G2. The result spills into G2:K21. Each row is kept together, so every Pokemon keeps its own Name, Types and Total.
Note: Empty cells in the array are returned as 0, not as empty cells. That is why Type 2 shows 0 for Pokemon like Charmander (row 15).
Note: The cells that the result spills into must be empty. If they are not, SORT returns a #SPILL! error.
Sort From A to Z
Leave out sort_index and sort_order to sort by the first column, from smallest to largest. For text, that is A to Z.
Sort the Pokemon names alphabetically. Type the label Name in M1, and this formula in M2:
| M | |
|---|---|
| 1 | Name |
| 2 | Beedrill |
| 3 | Blastoise |
| 4 | Bulbasaur |
| 5 | Butterfree |
| 6 | Caterpie |
| 7 | Charizard |
| 8 | Charmander |
| 9 | Charmeleon |
| 10 | Ivysaur |
| 11 | Kakuna |
| 12 | Metapod |
| 13 | Pidgeot |
| 14 | Pidgeotto |
| 15 | Pidgey |
| 16 | Raticate |
| 17 | Rattata |
| 18 | Squirtle |
| 19 | Venusaur |
| 20 | Wartortle |
| 21 | Weedle |
=SORT(B2:B21)
The array is only one column, so SORT sorts by that column. Beedrill comes first and Weedle last.
Sort a FILTER Result
You can put the FILTER function inside SORT. FILTER picks the rows, and SORT puts them in order.
List the Normal Pokemon, from the highest Total to the lowest. Replace the formula in G2 with this one:
| G | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total |
| 2 | 18 | Pidgeot | Normal | Flying | 479 |
| 3 | 20 | Raticate | Normal | 0 | 413 |
| 4 | 17 | Pidgeotto | Normal | Flying | 349 |
| 5 | 19 | Rattata | Normal | 0 | 253 |
| 6 | 16 | Pidgey | Normal | Flying | 251 |
=SORT(FILTER(A2:E21, C2:C21="Normal"), 5, -1)
FILTER returns the five Normal Pokemon in the same order as the table. SORT then sorts those rows by their 5th column, Total. Pidgeot (479) comes first and Pidgey (251) comes last.
Excel works from the inside out: first FILTER, then SORT.
Sort Command vs SORT Function
| Sort command | SORT function | |
|---|---|---|
| How you use it | Click a Sort command in the ribbon | Type a formula in a cell |
| The result | The data itself is put in a new order | A sorted copy, where you type the formula |
| When the data changes | You must sort again | The copy updates by itself |
| Excel versions | All versions | Microsoft 365, Excel 2021, Excel 2024 and Excel for the web |
Excel also has a SORTBY function, which sorts a range by the values in another range, so the column you sort by does not have to be part of the result.
See also: FILTER returns the rows that match a condition, and UNIQUE lists the different values in a range.
