The SORT function sorts the rows of a given array or range by the values in one or more columns.
Syntax
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])
range – The data range/group of cells used for the sorting.
sort_column – The column that the data will be sorted by.
is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order. FALSE sorts in descending order.
sort_column2 – [Optional] Use this parameter to sort the data with multiple columns.
is_ascending2 – [Optional] Similar to parameter 3. Use this to sort the data range with multiple columns.
Sample Usage
=SORT(A2:C10, 1, TRUE)
//This sorts the data in the first column in the range A:2:C10 in ascending order.
Learn how to use the Google Sheets SORT function and examples of its variations.
What is SORT Function in Google Sheets
While analyzing your data in Google Sheets, you may need to rearrange data based on a specific dimension. For example, arranging customer names alphabetically or arranging revenue in descending order. You may also need a combination of such ascending-descending rules on more than one column. SORT function in Google Sheets can help you with this.
Using the SORT function in Google Sheets
Naming the data range
- We will use a dataset for querying. For ease of querying, we will name the range so we do not need to select the data range every time.
- Click Ctrl + A (Windows) or Cmd+A (Mac) to select the entire data range.
- Go to Data
- Select Named Ranges
2. Naming the range
- Give a suitable name to your data range. Here I am giving it a Dataset.
- Click on Done to complete the process of naming.
Sorting using one column in ascending order
- To sort the data range by a single column in ascending order, use the syntax:
=SORT(range, column_index, ascending_order)- To sort the dataset by a specific column, use the following syntax:
=SORT(Dataset,1,TRUE)- Here, I have used the named range Dataset as the range to sort.
- I have used column number 1, i.e. Name to sort the dataset.
- I have kept order as TRUE, which sorts the order in ascending order.
The output will be as shown below.
Sorting using one column in descending order
- To sort the data range by a single column in descending order, use the syntax:
=SORT(range, column_index, ascending_order)- To sort the dataset by a specific column, using the syntax, use:
=SORT(Dataset,1,FALSE)- Here, I have used the named range Dataset as the range to sort.
- I have used column number 1, i.e. Name to sort the dataset.
- I have kept order as Descending. Therefore, the output is:
Sorting using multiple columns
- To sort the data range by multiple columns, use the syntax :
=SORT(range, column_index1, ascending_order1, column_index2, ascending_order2)- To sort the dataset by multiple columns, using the syntax, use :
=SORT(Dataset,2,TRUE,3,FALSE)- Here, I have used the named range Dataset as the range to sort.
- I have used column number 2, i.e. Quarter to sort the dataset in ascending order. In the case of multiple columns, the first column gets the priority for sorting.
- If the Quarter values are the same, the dataset will be sorted using column number 3, i.e. Sales to sort the dataset in descending order, as shown below:
Sorting with Column References
The syntax to sort a range by column reference is:
=SORT(range, column_reference, ascending_order)Filling the parameters, we’d have something like the following:
=SORT(A2:D20, B2:B20, 1)- Here, A2 to D20 is the range to sort
- B2:B20 is the column reference
- Note: The number of columns and rows returned by the SORT function should be the same as the input range, therefore, the column reference also will change if we change the range.
- 1 is to sort the column in ascending order
Upon completing the syntax, we’d get the result as shown below:
Example 5: Horizontal Sorting using Google Sheets Sort function
The syntax to sort the data horizontally is as given below.
=TRANSPOSE(SORT (transpose (range, column_number, ascending_order)Entering the parameters with our own, we have something like the following:
=TRANSPOSE(SORT (transpose (Dataset), 1, true)- The inner transpose is used to transpose the entire dataset, i.e. replace the columns with rows and vice versa.
- They are then sorted using the SORT function.
- The outer TRANSPOSE is used to transpose the sorted dataset back to the initial view.
Entering the formula returns the result as shown below.
Conclusion
The SORT function in Google Sheets can be used to sort data as per convenience. Some of the most frequently used applications were discussed in the article. If you want to know more about SORT function in Google Sheets, go through the official documentation here.
See Also
You can checkout other equally good articles on Google Sheets here.
How to Create Google Calendar Events from Google Sheets: Learn how to create Google Calendar events from Google Sheets.
ARRAYFORMULA in Google Sheets: Learn how to use the ARRAYFORMULA function in Google Sheets and its variations.
Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.