Mehrab Imtiaz
Today, we will look at how to sort by multiple columns in Google Sheets. While most users will think about the Sort Range option, which we will be discussing in detail, there are other more unorthodox approaches available.
Let’s get started.
3 Ways to Sort by Multiple Columns in Google Sheets
1. Using Sort Range Option to Sort Multiple Columns in Google Sheets
Using Sort Range is the primary way to sort by multiple columns in Google Sheets. Over the years, the layout of various menus in the applications may have changed, including how Sort Range is presented, but most things have remained the same.
Like our generic practice worksheet for today:
Here, we have four different columns of data that can be used for sorting. For example, we can sort the table according to the ascending order of the ID of each employee.
Since we are working with multiple columns, the first step we should take, after selecting the whole table, is to open the Sort Range window. We can navigate to it through the Data tab > Sort range > Advanced range sorting options.
The Sort Range window:
We have to be aware of certain options in this window. Like, if we have the headers of our table selected, we must make sure that the Data has header row option checked.
With that in mind, let’s apply the ascending order condition (Sort A-Z) on the ID column.
Multiple Column Conditions (Hierarchy)
What we’ve just shown sorts the entire table according to a single column. But with the advanced Sort Range options available in Google Sheets, we can sort the table by multiple column conditions. Let’s see how it’s done.
For this example, we want to sort our table by Department name first and then by the Start Date of each employee.
Now, back in our Sort Range window, click on Add another sort column button to input the second option. It should look something like this:
Click Sort to apply and see the result.
Like this, you can add multiple column conditions (hierarchy) to your sorted table.
2. Using the SORT Function to Sort Multiple Columns
Another way to sort data by multiple columns in Google Sheets is by using the SORT function. Not only does this function sort data within a given range, with conditions if I might add, it also helps to extract data to another location since it is a function to be applied to another cell.
Sort function syntax:
Note that the SORT function only extracts data, so do not include headings in the range selection.
Let’s sort the table by Names in alphabetical order. Our formula:
=SORT(B3:E12,2,TRUE)
As we can see, like the advanced Sort Range of Google Sheets, the entire table was sorted according to a column in Google Sheets.
The 2 in our formula represents the second column of the selection, the Name column, which is our sorting column.
The input TRUE represents the ascending_order condition. We can also input 1 instead and 0 for FALSE.
Following this process, let’s add multiple column conditions to the Sort function.
Similar Readings
Sort by Multiple Column Conditions Using the SORT function
From the SORT function syntax, we see that we can input multiple column conditions. Knowing that we will try to achieve the same outcome as the last example of the previous section. Sort the table by Department name first and then by Start Date.
For our first condition, we have the formula:
=SORT(B3:E12,3,TRUE
Adding the second column condition, our formula becomes:
=SORT(B3:E12,3,TRUE,4,TRUE
Close parentheses and press ENTER. Our final formula:
=SORT(B3:E12,3,TRUE,4,TRUE)
Extra Tip
You can also designate the column range instead of the column number if your dataset is large and you are unable to determine the column number from it.
3. Using QUERY to Sort by Multiple Columns
Speaking of extracting data, another powerful function available to us in Google Sheets that can be used for sorting is the QUERY function.
Without going into much detail, the QUERY function takes a text query from the user and extracts the data accordingly, usually with conditions.
We will again use the Main worksheet as our data source.
From here, we will extract all the entries of the IT department in the ascending order of their starting dates from a separate worksheet.
Our formula:
=QUERY(Main!B1:E, "SELECT B, C, D, E WHERE D='IT' ORDER BY E ASC")
Formula Breakdown:
- Main!B1:E: Our range reference from the Main worksheet.
- SELECT B, C, D, E: The column reference from which the data will be extracted.
- WHERE D=’IT’: Our condition. It means to search column D for all instances of “IT”. Text conditions within the query must be enclosed in single quotes (‘’).
- ORDER BY E ASC: Our sorting condition. The extracted data will be sorted in accordance with the ascending order of column E.
Note: The entire query must be enclosed within quotation marks (“”) since the QUERY function only takes text input.
Final Words
That concludes all the methods we can use to sort by multiple columns in Google Sheets. While the basic ones are easy to utilize, the complex methods allow for more conditions. We hope that these come in handy for your day-to-day spreadsheet tasks.
Feel free to leave us any queries or advice you might have in the comments section below.
