Google Sheets comes with powerful tools such as sorting for data organization and analysis. You can sort data in ascending or descending order based on alphabets, numbers, colors, dates, and more. The built-in SORT function, or Sort Range tool, is what you can use to sort by multiple columns in Google Sheets.
Sorting a single column by alphabets or numbers is pretty straightforward.
But things get complicated when you wish to sort complex datasets with many columns.
Let’s quickly discuss sorting by multiple columns in Google Sheets in this article. It will be your go-to resource for data organization using Google Sheets, so read until the end.
Here are the two methods we are about to discuss. You can click on the links to jump to that particular section.
Copy the Example Google Sheet
Here is the Google Sheet we used in this article to demonstrate sorting multiple columns using the Sort Range tool and Sort function.
Click here to Copy Sample Sheet
Or else, if you do have your own Google Sheet ready. Then, make sure to follow me along and practice what we are about to discuss.
Now, without any further ado, let’s begin.
How do you sort by Multiple Columns in Google Sheets using the Sort Range Tool?
Sort Range tool allows you to sort the datasets by any number of columns.
All you need to do is select the entire table before using the Sort Range tool. Otherwise, the tool will only be sorting the data in selected columns, and the rest of the table data remains unchanged.
Here’s how to use the Sort Range tool,
- Open the Google Sheet
- Go to the spreadsheet containing the table with a huge number of columns
- Choose any cell of the table and press “CTRL + A” on your keyboard. It will select the entire table
- Now, hover over the main menu and click on the tab “Data.”
- Click on “Sort range” from the popup
- Next, choose “Advanced range sorting options“
- A new dialog box will open as shown below
- Tick the box before “Data has header row.”
- Click on the dropdown option beside “Sort by.”
- Choose the first column for sorting; in this case, we are going to start with “City”
- The city of each employee needs to be sorted in ascending order, so tick the circle before “A to Z.”
- Next, click on the “Add another sort column” button
- Here, you can select the second column for sorting, click on the dropdown, and choose “Department.”
- The department also needs to be sorted in ascending order. So, let us choose “A to Z” again
- Click on the “Add another sort column” to sort the third column
- For the third column, we will select “Full Name” using the dropdown
- This time, we’ll sort the annual salary column in descending order. Tick the circle beside “A to Z.”
- Click on the button “Sort.”
Most commonly, people forget to select the entire table or at least the entire column while using the Sort Range tool. It leads to an error where you will find that after clicking on the “Data” tab from the main menu, the option “Sort range” is grayed out and not clickable at all.
To avoid such errors, please make sure to select the entire table before using the Sort Range tool in Google Sheets.
How do you sort by Multiple Columns in Google Sheets using the SORT function?
The sort function will create a new table with dynamic values that will auto-update whenever you change something in the main table.
It is an array formula. The general syntax for the Sort function is as follows,
=SORT(range, sort_column, is_ascending)
Here is a quick explanation for each argument,
- “range” lets you select the entire table where you wish to sort the columns
- “sort_column” is the exact column number where the data will be sorted in either ascending or descending order
- “is_ascending” gives you the control to sort data in ascending or descending order. If you type “TRUE,” it will sort data in ascending order. Similarly, if you will put “False,” then it will sort the data in descending order
Let’s learn to use the sort function for sorting by multiple columns in Google Sheets.
Step #1 – Create A New Table
To use this function, you must create a new table in the same spreadsheet or a separate one.
For the sake of this article, we will do this in the same spreadsheet as below.
Step #2 – Sort the data by Multiple Columns
It is the main step of using the Sort function for sorting the dataset.
- Click on the cell below the first header, “City.”
- Type “=SORT”
- Select the first option from the popup or press the “Tab” on your keyboard
- Now, select the table without headers, as shown below
- Press “,” on your keyboard to move to the next argument
- Enter the first column number where you wish to sort the date. Let us put “1” as we are planning to sort the City column
- Press “,” on your keyboard
- Type “TRUE” for sorting the City column in ascending order
- After pressing “,” on your keyboard, you will notice that the function will ask you to put the number for the second column for sorting
- Type “2” to sort the next column “Department”
- Press “,” on your keyboard
- As we will sort the Department column in ascending order, we’ll put “TRUE” again
- Follow the same process for the next column, which is “Full Name,” and make sure to type in “True” in the place of “is_ascending3, …” as we will sort this column in ascending order
- Next, you need to close the brackets by typing in “)”
- Press “Enter” on your keyboard
It will instantly create a new table, which is sorted by City in ascending order in the first column, Department in ascending order in the second column, and Full Name in ascending order in the third column.
PRECAUTION: The most common mistake while using the Sort Function
You must keep all the cells of the new table empty while using the Sort function.
Otherwise, it will return the error “Array result was not expanded because it would overwrite data in F11,” as shown in the following image.
Conclusion
Sorting multiple columns using the Sort Range tool is easy as you have to deal with the visual interface rather than typing in the formula manually, as in the case of the Sort function.
However, if you wish to create a dynamic table that auto-updates every time you make changes in the main table, we suggest you go for the Sort function.
I hope this article helped you learn to sort data by multiple columns in Google Sheets.
Feel free to comment below if you have any doubts or are stuck somewhere while using either the Sort Range tool or Sort Function. I will try to answer all of your questions as soon as possible.