The Excel SORT function is a lookup and reference formula that sorts the values in a range or array. The SORT function is one of the newest Dynamic Array functions which are release with Excel 2019. In this guide, we’re going to show you how to use the SORT function and also go over some tips and error handling methods.
Supported versions
- At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it's ready, the feature is planned for release for Office 365 users.
Syntax
Arguments
array | The range or array that you want to sort. |
[sort_index] | Optional. A numeric value indicating whether the list is to be sorted by row or column. The default value is 1. |
[sort_order] |
Optional. A numeric value indicating the sorting order.
|
[by_col] |
Optional. A Boolean value that specifies the sorting direction.
|
Examples
Note that we're using named ranges in our sample formulas to make them easier to read. This is not required.
Basic usage
Sort by any column in the desired order
Sort by multiple column
You can use different ordering types for different columns by entering another array into the [sort_order] argument. Once again, the order of the arguments are important. The first value in the array determines the first column of the array in the [sort_index]. As a result, entering {-1,1} to the [sort_order] means sorting the 2nd column in descending order and the 1st column in ascending order.
Sort top 3 column
Tips
- Currently, this formula is only available on Office 365 which comes with a subscription.
- The SORT function can be used as an alternative to the Sort feature without modifying the actual data.
- For horizontal ranges like A1:F1, set the [by_col] argument to TRUE or 1. =SORT(A1:F1,1,1,TRUE)
- Use Excel SORT function in combination with the UNIQUE function to create a sorted list of unique items. =SORT(UNIQUE(A1:F1,1))
- If you need to sort a range or array based on a different range or array, the SORTBY function is usually a better choice. The SORTBY function can handle adding/deleting columns, because it references a range rather than index number like the SORT function.
Issues
#SPILL!
If there isn't enough space for adding the results below the formula, you will see a #SPILL! error. Excel marks the target range with dashed lines. Clear the contents of the cells in this range, and Excel will automatically update the results.
#VALUE!
- If [sort_index] value is not between 1 and the column (or row according to sort direction) count, the function returns #VALUE!
- If [sort_order] argument is neither 1 nor -1, the function returns #VALUE!