This guide covers everything you need to know about the Google Sheets SORT function, including its definition, syntax, use cases, and how to use it.
What is the SORT Function? How Does It Work?
The SORT function in Google Sheets returns a sorted array or range of data based on specified columns’ values. The user can specify whether the sorting should be in ascending (from smallest to largest) or descending order (from largest to smallest) by using TRUE for ascending and FALSE for descending. Notably, the SORT function does not alter the original data; it returns a new sorted array or range without changing the original one.
Apart from sorting by a single column, the SORT function allows for sorting by multiple columns. This is particularly useful when you have rows with similar values in the primary sorting column. In such cases, you can specify an additional column (or columns) for sorting. The function then proceeds to sort by the first column, and for rows with similar values in the first column, it sorts by the subsequent column(s). The order of precedence for sorting follows the order in which you specify the columns.
While the SORT function organizes your data based on specific columns, it’s important to note that other columns in your range or array are returned in their original order. This means that while the rows’ order might change based on the sorting, the individual data within each row remains unchanged.
For instance, if you had a spreadsheet of students’ grades and wanted to sort them by the final exam scores, the SORT function would return a new array where the rows (representing each student) are arranged based on the values of the final exam scores. The individual student’s scores across different tests and assignments would remain in their original order within their respective rows.
SORT Syntax
The syntax and arguments for the function are as follows:
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])
Where:
- ‘range’ refers to the data that you want to sort. This can be a specific array or range of cells in your Google Sheets.
- ‘sort_column’ is the index of the column in the range or a range outside of the range containing the values by which to sort. It should be noted that a range specified as a ‘sort_column’ must be a single column with the same number of rows as the ‘range’.
- ‘is_ascending’ is a TRUE or FALSE value that indicates whether to sort ‘sort_column’ in ascending order. If FALSE is specified, the ‘sort_column’ will be sorted in descending order.
- ‘sort_column2, is_ascending2, …’ are optional arguments that you can add if you want to sort by additional columns beyond the first one. The sort order flags need to be specified for each additional column you want to sort by, in order of precedence.
Usage notes related to syntax and arguments:
- The ‘range’ is only sorted by the specified columns. Any other columns are returned in the order they originally appear.
- The ‘sort_column’ argument must be a single column with the same number of rows as the ‘range’.
- The ‘is_ascending’ argument is a boolean value, meaning it only accepts TRUE or FALSE.
- If you want to sort by more than one column, add additional ‘sort_column’ and ‘is_ascending’ arguments. These must be added in the order of precedence you want for the sorting.
- The SORT function does not modify the original data. It returns a new array or range of cells sorted based on the specified criteria.
- The SORT function can handle text, numbers, and dates. However, it’s important to ensure the data types in your ‘sort_column’ are consistent. Mixing different data types in the same ‘sort_column’ may lead to unexpected results.
Examples of How to Use the SORT Function
Here are some practical examples of how to use the SORT function in Google Sheets:
Example #1: Sorting a List of Names in Alphabetical Order
Let’s say you have a list of names in column A from A2 to A10 that you want to sort in alphabetical order. Here’s how you can do it:
=SORT(A2:A10, 1, TRUE)
In this formula, A2:A10 is the range of cells that you want to sort. The second argument 1 indicates that you want to sort by the first column in the range. The third argument TRUE means that you want the sort to be in ascending order (A to Z). If you wanted to sort in descending order (Z to A), you would use FALSE instead.
Example #2: Sorting a List of Numbers in Descending Order
Now, let’s say you have a list of numbers in column B from B2 to B10 that you want to sort in descending order. Here’s how you can do it:
=SORT(B2:B10, 1, FALSE)
In this formula, B2:B10 is the range of cells you want to sort. The second argument 1 indicates that you want to sort by the first column in the range. The third argument FALSE means that you want the sort to be in descending order (from highest to lowest). If you wanted to sort in ascending order (from lowest to highest), you would use TRUE instead.
Example #3: Sorting a Range of Cells by Multiple Columns
You can do that if you have a range of cells with multiple columns that you want to sort by more than one column. Let’s say you have a range of cells from A2 to C10, and you want to sort by column B in ascending order and then by column C in descending order. Here’s how you can do it:
=SORT(A2:C10, {2, 3}, {TRUE, FALSE})
In this formula, A2:C10 is the range of cells you want to sort. The second argument {2, 3} indicates that you want to sort by the second column and then by the third column in the range. The third argument {TRUE, FALSE} means that you want the sort by the second column to be in ascending order and the sort by the third column to be in descending order.
Why Is SORT Not Working? Troubleshooting Common Errors
If you’ve been using the SORT function in Google Sheets and are encountering errors, don’t panic. It is common to run into some issues, especially when you’re just starting to familiarize yourself with this function. In this section, we will discuss some of the most common errors, their causes, and how to resolve them.
#VALUE! Error
Cause: This error typically occurs when the range of cells you’re trying to sort contains non-numeric values. The SORT function in Google Sheets can only sort numeric and text data. So, if your range includes cells with error values, blank cells, or Boolean values (TRUE or FALSE), you’ll get a #VALUE! error.
Solution: To fix this, ensure that your range only contains numeric or text data. You can do this by manually checking your range and removing or correcting any non-numeric or non-text data. Alternatively, you can use the IFERROR function to handle cells with error values and avoid the #VALUE! error.
#REF! Error
Cause: The #REF! error is often caused by an invalid range reference. This happens when you delete or move cells included in your SORT function’s range.
Solution: To fix this, you need to update your SORT function to reflect the current location of your data. If you’ve deleted cells, you may need to add them back or adjust your range to exclude those cells.
#N/A Error
Cause: The #N/A error appears when the SORT function cannot find the value it’s supposed to sort. This could be because the value doesn’t exist in the range or because it’s a data type that the SORT function can’t handle.
Solution: If the value doesn’t exist in the range, you’ll need to add it. If the value is a type of data that the SORT function can’t sort, you’ll need to convert it to a type that can be sorted. For example, if the value is a date, you could convert it to a numeric value representing the number of days since a certain date.
Circular Reference Error
Cause: A circular reference error occurs when your SORT function refers to the cell it’s in, creating a loop that Google Sheets can’t resolve.
Solution: To fix this, you need to make sure your SORT function doesn’t refer to its own cell. You can do this by checking the range in your function and making sure it doesn’t include the cell where your function is.
Using SORT With Other Google Sheets Functions
Combining the SORT function with other Google Sheets functions can help you perform more complex data manipulations. This section will provide examples of how to use SORT with various other Google Sheets functions.
With QUERY
Usage: The QUERY function performs a query over a data range. It is especially effective when combined with the SORT function to filter and sort data.
Example: Suppose you have a sales data list and want to filter out all sales below $500 and sort the remaining data by sales amount.
In cell A1, you have the column headers: “Salesperson”, “Sale Amount”. The sales data starts from cell A2. You can use the following formula:
=QUERY(SORT(A2:B100, 2, FALSE), “select * where Col2 > 500”)
This formula first sorts the range A2:B100 in descending order based on column 2 (Sale Amount), and then filters out all rows where the sale amount is below $500.
With UNIQUE
Usage: The UNIQUE function returns unique rows in the provided source range, discarding duplicates. Combined with the SORT function, you can create a sorted list of unique values.
Example: Suppose you have a list of names in column A starting from cell A1 and want a sorted list of unique names. You can use the following formula:
=SORT(UNIQUE(A1:A100))
This formula first finds all unique names in the range A1:A100, and then sorts them in ascending order.
With COUNTIF
Usage: The COUNTIF function counts the number of times a specified value appears in a range. When used with the SORT function, you can sort data based on the frequency of occurrence.
Example: Suppose you have a list of names in column A starting from A1 and want to sort them based on how often they appear. You can use the following formula:
=SORT(A1:A100, COUNTIF(A1:A100, A1:A100), FALSE)
This formula first counts the frequency of each name in the range A1:A100, and then sorts the names in descending order based on their frequency.
For more details on the SORT function, check out the official documentation at the Google Docs Editors Help Center.
