Mehrab Imtiaz
Today, we will look at how we can use the SORT function to sort for multiple columns in Google Sheets. We will look at different use cases that are common for any regular user of Google Sheets.
Let’s get started.
SORT Function Basics
Let’s start by understanding the fundamentals of the SORT function.
SORT function syntax:
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
Function Breakdown:
- range: The range of data that is to be sorted and extracted.
- sort_column: The index of the column within our range selection by whose values our data will be sorted. You can also use the range of the column. You must use the range of the column if the sorting data is outside of the selected range.
- is_ascending: The sorting condition of the data. Takes TRUE/1 for ascending order or FALSE/0 for descending order.
- [sort_column2, is_ascending2, …]: This is Optional. You can add more column conditions to the sort in order of priority.
Simple Multiple Column Sorting Example With SORT Function
In this article, we will focus solely on how we can sort by multiple columns using the SORT function in Google Sheets. And to that end, we have created the following dataset to represent different types of values that we can use to sort.
As for our example, let’s say we want to sort our entries first by their Departments and then by each employee’s Starting Dates.
So, our sort conditions will be:
- Range: B3:E12
- For Department:
- sort_column: 3
- is_ascending: TRUE
- For Start Date:
- sort_column: 4
- is_ascending: TRUE
Our final formula:
=SORT(B3:E12,3,TRUE,4,TRUE)
That concludes the basic way in which we can use the SORT function to sort multiple columns in Google Sheets. But for more ways to sort by multiple columns, please visit our How to Sort by Multiple Columns in Google Sheets article.
With the fundamentals out of the way, let’s see some more examples of using this function in different scenarios.
More Examples: Using SORT Function for Multiple Columns in Google Sheets
1. Column References
The SORT function allows us to also use the column range instead of the index for the sort_column field of the formula.
Now, modifying the basic SORT formula with column range references we get:
=SORT(B3:E12,D3:D12,TRUE,E3:E12,TRUE)
One important thing to be careful about in this format is the range length. If the column range for one column differs from the other, you will get an error.
So make sure that all the column ranges are of the same length for all sort_column inputs.
Similar Readings
2. From a Different Column
For this example, we have created another worksheet in our workbook called Salary. Aptly named as it contains the salaries of all the employees of the Main worksheet.
Our task is to extract all data from the Main worksheet to the Salary worksheet and sort by Department first and then by the descending order of Salary.
Not only will we be bringing in data from another worksheet, but also have sort_column conditions from both Main and Salary worksheets.
Our formula:
Here, we can see that the data we have extracted, and the first column condition (Department) is referenced from another worksheet. Whereas the second column condition (Salary) comes from the current worksheet, and also it is in descending order (is_ascending is FALSE).
3. Horizontal Sort
We may sometimes have data arranged horizontally instead of having traditional vertical columns.
Using the SORT function on this arrangement of data will only end in errors.
With SORT being the traditionalist as it is, we are asked to rearrange the data within the function to make it work. And doing that is simple with the TRANSPOSE function.
We apply TRANSPOSE around the range field of the SORT function. Our formula:
=SORT(TRANSPOSE(C2:G3),1,TRUE)
However, if you want to keep the horizontal format, you can enclose the entire SORT formula in another TRANSPOSE function:
=TRANSPOSE(SORT(TRANSPOSE(C2:G3),1,TRUE))
Final Words
That concludes all the ways we can use the SORT function for multiple columns in Google Sheets. We hope that all the examples we have shown come in handy in your daily spreadsheet tasks.
Feel free to leave any queries or advice you might have for us in the comments section below.
