The excel sort function is one of the most useful new features in Excel. It is one of many functions that make advantage of Excel’s new dynamic array calculation engine, which enables Excel to spill results from a single formula across numerous cells.
Table of Contents
SORT function in Excel
The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.
SORT is a member of the family of dynamic array functions. As a consequence, a dynamic array is created that overflows vertically or horizontally onto nearby cells, depending on the form of the source array.
The SORT function has the following syntax:
=SORT(array, [sort index], [sort order], [by col])Where:
- Array (needed) – represents a collection of values or a range of cells to sort. These may be any kind of value, such as text, numbers, dates, or timings.
- Sort index (optional) – an integer indicating the column or row from which to sort. If this parameter is missing, the default index 1 is used.
- Sort order (optional) – specifies the order in which items are sorted:
- 1 or omitted (default) – ascending order, that is, from smallest to greatest -1 – descending order, that is, from greatest to smallest
- By col (optional) – a logical value indicating the sort direction:
- FALSE or omitted (by default) – row-based sorting. This is the most often used option.
- TRUE – column-based sorting. This option is appropriate if your data is structured horizontally in columns.
Notes on use
The excel sort formula uses a formula to organize the contents of a range or array in ascending or descending order. The outcome of SORT is a dynamic array of data that “spill” into a range on the worksheet. If the values in the source data change, the SORT result will immediately update.
The SORT function accepts four parameters: an array, a sort index, a sort order, and a by col parameter. The first option, array, specifies the array or range to sort. This is the only argument that is necessary. By default, the SORT method sorts data upward using the array’s first column. Utilize the optional sort index and sort order parameters to specify which column to sort by and in what order to sort (ascending or descending). Sort index should be an integer that corresponds to the sorting column (or row). For instance, to sort by the third column in a set of data, use the sort index 3 parameter. The optional sort order parameter specifies the direction of the sort. Ascending order is 1 and descending order is -1. The SORT function by default sorts data vertically by row. Set the fourth option, by col, to TRUE to sort a range horizontally by columns.
Excel sort formula – Tips and Tricks
Excel sort function is a novel dynamic array function, and as such, it has a few quirks worth noting:
Currently, only Microsoft 365 and Excel 2021 support the excel sort function. Because Excel 2019 and Excel 2016 do not support dynamic array formula, they do not have the SORT function.
If the array generated by a SORT formula is not provided to another function, Excel dynamically builds and populates a suitably sized range with the sorted data. As a result, always ensure that there are sufficient empty cells below or to the right of the cell where the formula is entered; otherwise, a #SPILL error occurs.
The findings are continuously updated in real time as the underlying data changes. The array given to the formula, on the other hand, does not immediately expand to accept additional elements inserted outside of the referred array. To include such things, you must either change the array reference in your formula, convert the source range to a table, or build a dynamic named range.
Excel’s SORT Function Constraints
The SORT function cannot be used to sort columns or rows that are not contiguous.
For instance, you cannot sort the employee names and wages separately since they are not adjacent.
However, you may sort by employee name and date of hire, or by employee name and date of hire and salary.
If you ever need to sort non-adjacent columns, you may use the approach outlined in this article.
If you wish to sort any range of cells according to a row or column outside that range, you cannot use the SORT function.
For instance, you cannot use the SORT function to sort just the employee names and joining dates by salary.
To resolve these sorts of issues, you may utilize Excel‘s SORTBY function. Alternatively, follow the process outlined in this article.
Arrange and Rank
In this article, we’ll examine one Dynamic Array Functions: SORT. It is excellent for showing a list in a ranked or chronological sequence (date, time).
In our example, we’re going to classify workers according to the amount of time they spend in the office (duration) over the course of a week. This will rank the workers from those who spent the greatest time at work to those who spent the least.
However, before we begin sorting, allow me to demonstrate quickly how I combined all of the duration periods for each employee using the SUMIF function.
The SORT Feature
Along with ordering the personnel from most to least hours worked, it would be beneficial to provide the matching hours next to each name. There are other methods to achieve this, however we will utilize the SORT function. This is another Dynamic Array Function that returns a wide range of values.
To use SORT, you only need to provide the range to sort and the order in which it should be sorted. (The SORT function’s second parameter, sort index, is not required in this case.)
Our straightforward formula would be as follows:
=SORT(B2:B8,,-1)
Video: How to Use Excel SORT Function
This Excel video tutorial, we’re going to explore the powerful SORT function, a dynamic array function in Excel that allows you to sort data alphabetically or numerically by columns or rows.
Related Functions
- Excel Filter function
The Excel FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…