The SORT function lets you sort a range (or array) of data. The function creates a new range of data with the new, sorted output from the function. SORT is often used with or instead of the FILTER function when widdling down large amounts of data. FILTER is for reducing data and SORT is for, well, sorting it.
The SORT function is typically accessed from the user menu, but it can also be typed into a cell just like any other function. Excel, the most popular spreadsheet program, only has it as a menu function so learning to use SORT as a function can be a new experience even to long-time spreadsheet users.
Video Explanation
Purpose
Returns sorted data from your specified source.
Syntax
=SORT(range,sort_column,is_ascending,[sort_column2, is_acending2,...])
range
– The entire range of data that is to be sortedsort_column
– The column of data that contains the criteria that you are sorting. Can only be one column at a time.is_ascending
– Two possible answers – TRUE for ascending (1,2,3), FALSE for descending (3,2,1)
Tip: When using SORT, start your formula in a cell that should be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper left cell and can be changed.
Examples
Example 1
Using a simple SORT function with one column of data to be sorted in ascending order. The first rows are your raw data, then the rows below the SORT formula are the output.
A | |
1 | 2 |
2 | 5 |
3 | 3 |
4 | =SORT(A1:A3,1,TRUE) |
5 | |
6 |
A | |
1 | 2 |
2 | 5 |
3 | 3 |
4 | 2 |
5 | 3 |
6 | 5 |
Example 2
Let’s look at an example now of sorting a table with two columns of data and sorting in descending order with the second column as the value to sort by.
A | B | |
1 | 2 | b |
2 | 5 | t |
3 | 3 | r |
4 | =SORT(A1:B3,2,FALSE) |
|
5 | ||
6 |
A | B | |
1 | 2 | b |
2 | 5 | t |
3 | 3 | r |
4 | 5 | t |
5 | 3 | r |
6 | 2 | b |
Example 3
Two column SORT, sorted by only column 2 in ascending order.
A | B | |
1 | Huey | Duck |
2 | Dewey | Duck |
3 | Louie | Duck |
4 | Duckworth | Butler |
5 | Bubba | Caveduck |
6 | Tootsie | Triceratops |
7 | =SORT(A1:B6,2,TRUE) |
|
8 | ||
9 | ||
10 | ||
11 | ||
12 |
A | B | |
1 | Huey | Duck |
2 | Dewey | Duck |
3 | Louie | Duck |
4 | Duckworth | Butler |
5 | Bubba | Caveduck |
6 | Tootsie | Triceratops |
7 | Duckworth | Butler |
8 | Bubba | Caveduck |
9 | Huey | Duck |
10 | Dewey | Duck |
11 | Louie | Duck |
12 | Tootsie | Triceratops |
Example 4
Two column SORT, sorted by column 2 in ascending order then by column 1 in ascending order. Now Dewey comes before Louie.
A | B | |
1 | Huey | Duck |
2 | Dewey | Duck |
3 | Louie | Duck |
4 | Duckworth | Butler |
5 | Bubba | Caveduck |
6 | Tootsie | Triceratops |
7 | =SORT(A1:B6,2,TRUE,1,TRUE) |
|
8 | ||
9 | ||
10 | ||
11 | ||
12 |
A | B | |
1 | Huey | Duck |
2 | Dewey | Duck |
3 | Louie | Duck |
4 | Duckworth | Butler |
5 | Bubba | Caveduck |
6 | Tootsie | Triceratops |
7 | Duckworth | Butler |
8 | Bubba | Caveduck |
9 | Dewey | Duck |
10 | Huey | Duck |
11 | Louie | Duck |
12 | Tootsie | Triceratops |
Live examples in Sheets
Go to this spreadsheeet for several examples of the SORT function that you can study and use anywhere you would like.