Today, we want to learn how the SORT function works in Excel.
The SORT function is available from Dynamic Array Excel, which is around 2021. Let’s start with the simplest version of the SORT function, where we only need to write an array. We have to remember that the array must be given without headers. Let’s select our data and put it as an argument and let’s put our formula into a cell (Fig. 1)
=SORT(A2:C169)
And we have the results. The most important thing about formulas in Dynamic Array Excel is that they spill. It means that our formula is in only one cell, but the results spill. The formula is only in cell E2, but when we click on cell E3, we can see that the formula is grayed out in the formula bar. It means that it contains results of this function, but doesn’t contain the function itself (Fig. 2)
When we add an array with data to be sorted, Excel’s sorting will be based on the first column, which is the column with dates. In the sorted array we don’t see dates but numbers because Excel doesn’t know how to copy the formatting in array formulas. If we want to have proper formatting, we can copy the formatting using the Format Painter (2) from the Home tab (1). We will also highlight the cell with the formula (3) (Fig. 3)
Now, our results are formatted. Since we sorted by the first column, our results are basically the same as in our original column. To change that, we can add the second argument to our SORT function, which is the sort index. It’s the number of a column by which we want to sort our data. Let’s write 2, meaning the second column (Fig. 4)
=SORT(A2:C169,2)
We can see that the sorting has changed. Now, we can see that only Chandlers are at the top because it’s an ascending sorting. This way we can sort by any column. Let’s move on to the third argument, which is the sort order. Let’s change it to a descending sorting by writing ‑1 (Fig. 5)
=SORT(A2:C169,2,-1)
Now, the first salesman in Ross. Let’s add something more. Let’s sort by Sales from the largest to the smallest. Now, the sales are written randomly in the Sales column. We can add the third column to the second argument. We can also add a reference to cells where numbers of columns are written. It’s very important to write them in the correct order. In our case the first cell from the top contains 2 and the second one contains 3. It means that first, we will sort by the second column, and then by the third column (Fig. 6)
And we have the results. We can see that Ross is sorted by values in the Sales column. The same is with the rest of the salesmen. Now, let’s focus on the third argument once again. We have two columns of sorting in the second argument, but only one number (-1) in the third argument which is the sort order. It means that the sorting is descending for each column. When we want to have an ascending order for one column and a descending order for another column, we can do the same as we did with the second argument. We have to refer to some cells in Excel. In our case we have a sorting order for each column (Fig. 7)
Now, we have an ascending sorting for the Salesman column, and a descending sorting for the Sales column.
When we don’t want any additional cells in our sheet, we can hard code them in the formula by pressing F9 key. It changes the argument into an array. We can do the same with the sort order argument. When we look at the formula now, we can see that the second column in ascending, and the third column is descending (Fig. 8)
Now that we have everything we needed, we can cancel the unnecessary cells. We don’t even need the third argument, as it is reserved for horizontal sorting. Here, we are sorting only by columns, so we can leave it like that. Here are our results (Fig. 9)
