It’s always easier to work with data that has been organized and sorted in a particular order.
They are easier to sift through and it’s easier to find data items of interest.
This is why sorting is one of the most frequently used features in Google Sheets.
What is the SORT function Used For?
As the name suggests, the SORT function helps us sort data in a range of cells and returns the sorted output in a new cell range.
This function lets us sort data in ascending or descending order. It also lets us sort data by one or multiple column criteria.
Difference between SORT Menu and SORT Function
Google Sheets provides two ways to sort data:
- Using the ‘Sort’ menu (can be found under the ‘Data’ menu).
- Using the SORT function
While the Sort menu can be accessed with a couple of clicks, the SORT function lets you enter the formula in the formula bar, giving you more control over the sorting process.
The main difference between the above two methods is that the Sort menu works on and changes the original data range.
The SORT function, on the other hand, displays the sorted data into a new data range, thereby keeping the original data unchanged.
Syntax of the SORT function in Google Sheets
The syntax for the SORT function is:
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])
- range is the group of cells that you want to apply the sort function on (the cells that you want to sort)
- sort_column is the main column by which you want to sort the data in the range.
- is_ascending specifies if the range should be sorted in ascending or descending order of the sort_column. It can either be TRUE or FALSE, with a FALSE representing a ‘descending’ order.
The above three parameters are compulsory in a SORT function, but you can also add additional parameters if you want to sort the range by more criteria. In that case,
- sort_column2 is the secondary column by which you can sort the data in range
- is_ascending2 specifies if we want to sort the range in ascending or descending order of sort_column2. This parameter can also be a TRUE / FALSE value.
You can add more [sort_column2, is_ascending2] pairs like this depending on the number of column criteria you need for sorting your data in the range.
Note: The sort_column can be specified either as a column index (a number representing the position of the column in the range), or as a reference (the range of cells in the column, eg: A2:A5). The number of cells in sort_column should be equal to the number of rows in the range, otherwise, the SORT formula returns an error.
Different ways of using the SORT Function (Examples)
Once you understand the syntax for the SORT function, applying it is quite easy.
We will take a look at two ways in which you can use the SORT function:
- When you want to sort by a single column
- When you want to sort by multiple columns
To explain both methods, we will use the following sample data. We are going to use the SORT function to sort the data shown below:
To help you easily see the differences between the original and sorted data, we are going to sort the above data and display it in the same sheet (in a neighboring cell range).
However, you can even enter the SORT formula in a new sheet and display the sorted data there.
Sorting by a Single Column
When you want to sort by a single column, just the first three parameters of the SORT function are enough to get the job done.
From our sample data, say we want to sort all the rows by Company Name (which is column A). Here’s how you can display the sorted data range starting from cell F2.
- Copy the data column headers and paste it onto the first row (cells F1 to I1 in our example).
- In cell F2, type following formula:=SORT(A2:D8,A2:A8,TRUE) or =SORT(A2:D8,1,TRUE).
- Press the return key
You should now see the entire sorted data occupy the range from cell F2 to I8. Note that the result of the SORT function is dynamic.
So, any changes you make to the original data get reflected in the sorted output as well.
Also, note that you cannot make any changes to the sorted output. Attempting to do so will result in a #REF error.
Sorting by Multiple Columns
If you look at the sorted data now, you will notice that some of the company names are repeating.
If you also want to ensure that the rows get further sorted by, say, decreasing order of Delivery date, whenever there’s a company name repeating, the SORT function makes that really easy too.
In this case, you can introduce the additional parameters into the SORT function. So you can change your SORT formula to:
In the above formula, we added two new parameters:
- We set sort_column2 to the column C2:C8 (The Delivery Date column)
- We set is_ascending2 to FALSE, since we want to sort in descending order of Delivery Date
This gives the following result:
You’ll notice in the above figure, that the data is first sorted in alphabetical order of Company Name.
Whenever there is a repetition of a name, the rows containing the repeated company names are then sorted in descending order of Delivery date.
For example, the company name Krajcik-Lowe appears in three rows. These rows are then sorted with the latest dates appearing first and the older dates appearing last.
You can even further refine this search if you need to, by adding more [sort_column2, is_ascending2] parameters.
In this tutorial, I showed you how you can use the SORT function in Google Sheets using single or multiple column criteria.
This function can be really helpful if you want to quickly sort and browse through your data, without having to affect the original data.
We hope this tutorial has been helpful to you.
Other Google Sheets tutorials you may like: