SORT in Excel: how to use it
SORT gives a sorted copy of your data that updates itself when the source changes. Available in Excel 2021 and 365.
Try the first exercises for freeIn the lesson The SORT function
What does SORT do?
SORT shows a sorted copy of your data somewhere else. Your original list stays as it is, and the copy updates itself when something changes.
With the second argument you choose which column to sort by, and with the third in which direction: 1 from low to high, -1 from high to low. SORTBY sorts by a column that you don't show yourself. Both work in Excel 2021 and 365.
When do you use SORT?
- A ranking that updates itself, from high to low: =SORT(A2:B20,2,-1).
- An alphabetical list of names: =SORT(A2:A50).
- Only the names, sorted by their score: =SORTBY(A2:A20,B2:B20,-1).
- A list without duplicates, in alphabetical order: =SORT(UNIQUE(A2:A100)).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
A sorted copy of your data that updates itself.
Filled in: =SORT(C2:C6,1,-1)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SORT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SORT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | What you want to show sorted: one column or a whole table. | C2:C6 |
[sort_index] | Optional: which column of your range to sort by. Count from 1. | 1 |
[sort_order] | Optional: 1 from low to high, -1 from high to low. | -1 |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
SORT step by step
E1 contains =SORT(C2:C6,1,-1). With -1 the highest value comes first. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1, with empty cells below it, and type SORT with a bracket.
=SORT( - Select what you want to see sorted, the revenue in C2:C6, and type a comma.
=SORT(C2:C6, - Type which column of your range to sort by: there's only one, so 1. Type a comma.
=SORT(C2:C6,1, - Type -1 for high to low, close the bracket and press Enter.
=SORT(C2:C6,1,-1)
More examples
Sorting the whole table
E1 contains =SORT(A2:C6,3,-1). Sort A2:C6 by the third column, the revenue: the whole row moves with it, and Leeds with 1200 is at the top.
Sorting by another column
E1 contains =SORTBY(B2:B6,C2:C6,-1). Only the products, but in the order of the revenue next to them.
In alphabetical order
E1 contains =SORT(A2:A6). Without other arguments, SORT sorts from A to Z.
Sorting with a formula or with the button?
The button changes your table itself. The formula leaves the source alone and updates automatically.
Where it often goes wrong
- You can't edit the result directly.
Practise this in the lesson The SORT function. The first exercises are free.
Frequently asked questions
Can I sort by two columns?
Yes, in Excel 365 with SORTBY.