The SORT function takes an array or range of values and performs a sort on the contents using the specified sort indices and sort orders for each respective sort key.
The array argument can be a hard-coded array of values, a range reference, or some more complex expression that results in various values (for example, using another dynamic array function like UNIQUE or SORTBY).
The sort index and sort order arguments can be scalar values in the case of a simple 1-key sort, or they can be array values (which must be the same length) that specify respective sort keys to use for a multiple-key sort.
The by col argument specifies whether the sort should be performed by rows (this is the default behavior) using the sort index to define key column indices, or instead by column using the sort index to specify the key row indices.
This function is useful for cases like the content of a table, where the key columns or rows are contained inside the table data.
For matters where the table data needs to be sorted based on a key value that is not part of the table data, use the SORTBY function instead (see this related blog.
You can find the examples in this blog for SORT in this Excel workbook.
SORT Syntax
| SORT(array,[sort index],[sort order],[by col]) | |
|---|---|
| array | Array or range to sort. |
| sort_index | Optional index of the row or column to sort by, or array of sort key indices (default is 1). |
| sort_order | Optional -1 to sort descending or array of values specifying the sort order for the sort_index array (default is 1, ascending). |
| by col | Optional TRUE to sort by column (default is FALSE, sort by row). |
The examples below use this SortData table:
Figure 1 SortData Table
The first example sorts on the ProductName column in the default ascending order:
=SORT(SortData,2)
Figure 2 Example 1
Note that the above result in I12:N22 is formatted to appear like a table but is not a table since dynamic array formulas and spilling behavior are not supported in table cells.
Instead, the range is formatted to appear like a table, and the table header cells I11:N11 are merely copies of the original table header cells in B11:G11.
The next example sorts on the Quantity in descending order:
=SORT(SortData,3,-1)
The last example sorts on the SalesPerson in ascending order, then Quantity in descending order:
=SORT(SortData,{6,3},{1,-1})
Figure 4 Example 3
Note that you can provide inline array arguments specifying the indexes, provide cell range references containing the indexes, or even calculate each index using an individual expression.
SORT in GrapeCity Products
The SORT function is supported in these GrapeCity spreadsheet controls and components:
- Spread.NET Windows Forms Spreadsheet Control | Demo
- SpreadJS Javascript/Typescript/Angular/React/Vue Spreadsheet Widget | Demo
- GrapeCity Documents for Excel .NET and Java Controls | .NET Demo | Java Demo
MESCIUS inc.