Description
Use this function to dynamically sort a range or array by one or more columns in ascending or descending order.
Syntax
SORT(array, [sort_column1], [sort_order1], [sort_order2], [sort_order3])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array |
Yes | The range or array to be sorted. | A cell, a cell range, or a formula which results in either of these. |
[sort_column1] |
No | The column number to sort by. First column (A) is 1. | A positive integer representing the column to sort, or a formula which results in a positive integer. |
[sort_order1] |
No | The sort order for the specified column. Default is 1 (sort in ascending order). | A positive integer with the value 1 (ascending), or -1 (descending), or a formula which results in one of these. |
[by_column] |
No | If the sorting is to be done by column. Default is FALSE (sort data vertically by rows). | TRUE or 1 = sort by column. FALSE or 0 = sort by row, or a formula which results in one of these. |
Examples
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Surname | Age | City | Salary |
| 2 | John | Smith | 35 | Philadelphia | 75000 |
| 3 | Alice | Johnson | 28 | Chicago | 65000 |
| 4 | Bob | White | 42 | Los Angeles | 85000 |
| 5 | Harry | Harrison | 33 | Chicago | 70000 |
| 6 | Alice | Kramden | 39 | Brooklyn | 72000 |
Sample formulas
| Use case | Formula | Result and Explanation | |||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Sort a dataset by a specific column in ascending order. |
=SORT(A2:E6, 3, 1)
|
This formula sorts the entire dataset by Age in ascending order. For this data set it would return the following:
|
|||||||||||||||||||||||||
| Sorts a data set by a specific column in ascending order, while preserving the original row relationships. |
=SORT(A2:E6, 4, 1, 0)
|
This formula sorts the entire range by the City column (column 4)
in ascending order, while preserving the original row
relationships. The For this data set it would return the following:
|
|||||||||||||||||||||||||
| Select a subset of cells, and then sort that result by another column. |
=SORT(FILTER(A2:E6, C2:C6>30), 5, -1)
|
This formula uses
FILTER
to select employees over 30
|
|||||||||||||||||||||||||
| Generate a list of names that has no duplicates. |
=SORT(UNIQUE(A2:A6), 1, 1)
|
This formula uses UNIQUE to build a list of names such that each name only occurs once in the list, and then sorts them alphabetically. For this data set it would return the following:
|
|||||||||||||||||||||||||
| Find the name of the oldest person in a data set. |
=TAKE(SORT(A2:B6, 2, -1), 1, 1)
|
This formula first sorts the data by age in descending order, so that the oldest person (Bob, age 42) appears first. TAKE then returns the first row's Name value. For this data set it would return the following:
|
Notes
The SORT function:
- Can sort by multiple columns with a specified order.
- Works with both static ranges and dynamic arrays.
- Supports both ascending and descending sort orders.
Tips
- If you have header row(s), start your array values one row down (for example in the data set above, start with A2 rather than A1) if you don't want the headers to be included.
- SORT can be combined with other functions, including:
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.