The SORTBY function is similar to SORT, but its arguments are created in a different way, and requires slightly less planning.
We have our list of employees, where we want to sort first by department, and then by hourly wage in ascending order.
Let’s start by selecting the first cell in our new table, and entering =SORTBY and left parenthesis.
Although SORTBY is an array function, curly brackets don’t need to be used due to the structure of the function.
We select for our first argument the range of cells we will use.
In this function, each instance of sorting comes in pairs.
We’ll therefore have to specify a column to sort, as well as the order in which it should be sorted.
As such, for our second argument, we select the column that we want to sort by first, that is, department.
In the next argument, we specify the sorting method.
For ascending, we enter one, and for descending, minus one.
We want department to be sorted in ascending order, so we’ll enter a one.
After that, we’ll do the same for the next sorting pair, which for us will be the hourly wage… and minus 1 for descending.
We’ll close the function and hit enter, and we’ll see that all the cells in the table have been filled in using the correct sort sequence.
Thanks to the structure of the function, we can now easily go back in and add a sorting, which is slightly more complicated in the SORT function.
We go into the tail end of the function, and add the Name column, and a one for ascending order, and when we hit enter, we see that the cells update automatically according to our new sorting.
The question of whether we should use SORT or SORTBY in any given situation is a matter of taste, depending on how accustomed we are to array formulas.
In cooperation with organisations and professionals within the industry, Learnesy have developed an Excel course that is adapted to those...
Looking for something custom made? Contact us and we will make sure to meet your requirements.