Tackle 2024 with new knowledge – take advantage of Learnesys’ fall campaign on the All Courses package. Read more here.
The SORT function allows us to sort a table on several different levels, all with one function.
In our example, we have a list of employees, where we first want to sort by department, then by hourly pay in ascending order, and finally by the number of hours in descending order.
By combining the SORT function with curly brackets, we can create a new table that uses our sorting.
We stand in the first cell of our new table and write =Sort and open parenthesis.
As the first argument, we’ll specify the array that we will use, that is, the original table.
For the second arguments, we need to specify which columns we want to sort, and in which order.
Here we have to use curly brackets to tell Excel that we will enter multiple columns, and we separate each argument within the parentheses with a comma.
Each column in the array has an associated number, and it starts with One on the leftmost column.
We want to sort by department, hourly wage, and number of hours, so we enter their corresponding numbers in that order, i.e. 2, 4, and 3, and close the bracket.
In the third and final argument, we specify whether we want to sort in ascending or descending order.
Again, we must use the brackets.
For ascending, we enter one, and for falling minus one.
We therefore enter sequences 1, -1, and 1.
The first number one corresponds to the first column in the previous argument, the second corresponds to the second, and so on.
We first close the array with a curly bracket, and then the entire function, and when we hit enter, all the cells in the table have been filled in, thanks to the spill behavior.
The columns are now all sorted according to our criteria, and are dynamically updated when the values in the table change.