The Google Sheets SORT function is one of the most powerful array formulas. To use this formula, you can either select the cell and type “=sort” or navigate to the “Insert” tab on the main menu and choose the “Function” option from the popup.
It allows you to sort the dataset by single or multiple columns in ascending and descending order.
You can sort spreadsheet columns alphabetically or numerically!
Sorting or filtering data in both Google Sheets and Microsoft Excel allows you to organize and visualize spreadsheet data like a pro.
Are you looking to improve your data analysis capabilities in Google Sheets?
This article is an in-depth guide about the SORT function in Google Sheets. We will discuss SORT function syntax, vertical sorting, horizontal sorting, and different examples. Make sure to read the article until the end because it contains helpful information meant to help you now and in the future.
Whether you are a beginner or an advanced user, this article is all you need to use the SORT function to sort single or multiple columns.
Table of Contents
Why use the SORT function?
Though there are several other built-in features in Google Sheets for sorting data in ascending and descending order, the SORT function is quite helpful in advanced cases.
The function allows you to create a dynamic table that auto-updates whenever you change something in the main table. Simply put, it creates a new table and sorts it according to the criteria you provide rather than altering the original dataset.
Hmmm… But do I need a separate table?
Consider, for example, that you have sorted a table using the Sort Range tool. If you edit a few cells or add new entries to the sorted column, it will mess up the sorting applied previously.
However, this is not the case with dynamic tables created using the SORT function.
In other words, if you change something or add new rows to the main data table, it will be instantly reflected in the newly created dynamic table, and sorting is adjusted automatically.
The Sort function eliminates the need for sorting the dataset every time you alter the table data.
SORT Function Syntax
The general syntax for the Sort function is as follows,
=SORT(range, sort_column, is_ascending)Each of the arguments of the above formula needs to be replaced with proper information. Let us discuss the meaning of each argument in the following section.
“range”
This argument represents the table or column that needs to be sorted. You can either enter or select the table or column range.
To sort a single column, as shown in the following image, you can use the following formula:
=SORT(A1:A)
To sort a table, you need to select the table range, as shown in the following image.
“sort_column”
In the case of a table containing multiple columns, this argument plays a crucial role.
It needs to be replaced with the number representing the position of the column you wish to sort.
Consider the following table where we plan to sort the Department column.
As the Department column number is at the first position, you will replace the “sort_column” argument with “1”.
“is_ascending”
This argument decides the sorting order for the selected column. You can either sort the column in ascending or descending order.
Here are the two values you can use in place of this argument:
- TRUE – The function will sort the data in ascending order
- FALSE – The function will sort the data in descending order
Note that in case you don’t provide any input value for the “is_ascending” argument, the Google Sheet will sort the data in ascending order by default.
Copy the Example Google Sheet
You can copy the following Google Sheet to practice the SORT function and examples discussed in this article.
Or else, if you have your own Google Sheet ready with you. Open it and follow me along as we will move through this article.
How to access the SORT function in Google Sheets
You have two options to use the SORT function in Google Sheets: Manually Typing the Formula and Inserting the formula using the Main Menu.
Manually typing the formula requires you to select the desired cell, which is usually the first cell below the first header of the newly created table.
On the other hand, to insert the formula using the main menu, you can follow the steps below,
- Hover to the main menu and click on “Insert” tab
- Choose “Function” from the dropdown
- Now, select the “Filter”
- Click on “SORT”
Both methods lead to similar results. Choose them according to your preference.
How to Sort in Google Sheets using the SORT Function
Using the SORT function is pretty straightforward. Let us divide it into two steps as below.
Step #1 – Create A New Table
You can choose to create the dynamic table in the existing or the new spreadsheet.
Make sure you use similar headers for the dynamic table to avoid confusion in future.
For now, we will create our new table in the same sheet as we have a table with only three columns, as shown below.
Step #2 – Sort Data using the SORT function
It is the main step that creates the dynamic dataset.
Let’s get started,
- Hover to the new table created in the previous step and select the first cell right below the first column header
- Type “=sort”
- Choose the first option from the popup or press “Tab” on your keyboard
- Next, the function will ask you for “range,” select the entire table range as shown below. Make sure you don’t include headers while choosing the table range
- Press “,” on your keyboard to move to the next argument
- Provide the column number that needs to be sorted, which in this case is “1”, as we plan to sort the Department column
- Press “,” to move to the next argument
- Type “true” to sort the data in ascending order
- Complete the bracket using “)”
- Press “Enter” on your keyboard
It will instantly create a new table that auto-updates whenever you change something in the main table.
Note that the SORT function is an array formula; you should avoid changing or editing anything in the newly created table except the Sort formula.
For instance, if I try editing the name in “F6” from “Emma Hill” to “Adam Park,” the entire table instantly disappears. To undo this outcome, you must delete the change you just made, as shown below.
Q. What is people’s most common mistake while using the Sort function?
You must keep all the new table’s cells empty before using the SORT function.
E.g. One of the cells contains the false entry “qwe$”, as shown in the following image.
As soon as you use the SORT function, it will return an error as “Array result was not expanded because it would overwrite data in E10.”
Q. How can I Sort Data in Descending Order using the SORT function?
As discussed in the SORT function syntax section, the “is_ascending” argument allows you to sort data in ascending or descending order.
To sort the data in descending order, you need to replace the “is_ascending” with the input value “false.”
The final formula will be as follows:
=SORT(A3:C14,1,false)How to Sort Multiple Columns in Google Sheets using the SORT Function
You may wish to sort the data from the multiple columns for complex data analysis. The Sort function is one of the best ways to do so.
Here are the steps,
Step #1 – Create A New Table
In contrast to the previous table we used to demonstrate sorting a single column using the Sort function, the following table contains numbers in one column.
As shown below, let’s create a new table for the dynamic dataset.
Make sure to use the same header names to avoid the future confusion.
We will be sorting the table by Department first and then by each employee’s age.
Step #2 – Sort Multiple Columns using the SORT function
Select the first cell right below the first header, as shown below
- Click on the first cell below the first header
- Type “=sort”
- Select the first option from the popup or press “Tab” on your keyboard
- Choose the table range and make sure you don’t include headers
- Press “,” on your keyboard to move to the next argument
- Put “1” as the Department column is at the first position of the table
- Press “,” to move to the next argument
- Next, we need to sort the Department column in ascending order, so let us replace the “is_ascending” with “true.”
- For the next column, you will see that the function is asking you for “sort_column2, …”
- Put “4” as the age column is at the fourth position of the table
- Press “,”
- Type “true” again as we plan to sort the Age column in ascending order
- Complete the bracket using “)”
- Press “Enter” on your keyboard
As discussed earlier, if the Sort function is not applied to the table, make sure to check if all the cells of the new table are empty.
Here is the screenshot of the final output,
Note that the sorting is applied to the Department column first and then to the Age column as shown above.
Q. How many columns can I sort using the SORT function?
There is no limit on the number of columns you can sort using the SORT function.
You can choose to sort any number of columns alphabetically and numerically in ascending and descending order.
How to Sort Data with Column Reference in Google Sheets using the SORT function
Sorting with Column Reference is identical to Column Index Number. The only difference is instead of providing the column number, you are supposed to provide the entire column for sorting.
Look at the following table, where we plan to sort the data by Employee Names (Full Name).
So, if we plan to sort the data with Column Index Number, the formula will be as below,
=SORT(A3:C14,2,True)On the other hand, to sort the data with the Column Reference number, the formula will become something like the below,
=SORT(A3:C14,B3:B14,true)
The results produced by both of these methods are similar. You can choose them as per your preference.
PRECAUTION
You must ensure you will choose all the cells from the selected table range. Failing to do so will return the error as “SORT has mismatched range sizes. Expected row count:12, column count:1. Actual row count:9, column count:1.”
Let’s say, for example, our table range is “A3:C14”, which includes 12 rows in total. While selecting the Column Reference, we will choose the range “B3:B11”, which makes nine rows in total.
The final formula will be as follows.
=SORT(A3:D14,B3:B11,true)After using this formula, it will lead to the error as we discussed above. Refer to the following image.
So, considering the above error, I think choosing to put the Column Index Number instead of selecting the Column Reference is more convenient.
Can I Sort Data Horizontally in Google Sheets using the SORT function?
Yes. It is possible to sort the horizontal data in Google Sheets using the SORT function.
If you need to go for an extra step here with the “Transpose” function in Google Sheets.
The general syntax is as follows,
=TRANSPOSE(SORT(TRANSPOSE(range,) column_index, ascending_order))Most of the time, the data is available vertically. In other words, the dimensions are available across the columns and values across the rows.
However, consider the following example: Department and Full Name entered across the first and second rows. All the values (departments and employee names) are available from column “B” to “H.”
The data is available horizontally in the above example, and the SORT function won’t be directly applicable. The Transpose function helps here.
Note that the SORT function is designed for vertical datasets. The Transpose function is an array formula that flips the rows and columns of the table.
We will be using the Transpose function twice. First, flip the “range” argument and tell the SORT function to calculate the sorting, considering that the table range is vertically arranged. Second, transpose the results produced by sorting the data using the SORT function.
The final formula will be as follows,
=TRANSPOSE(SORT(TRANSPOSE(B1:M2),1,true))Let us follow the two steps we did for sorting the column vertically in the above sections and see the above formula in action.
Step #1 – Create A New Table
We can choose to create the new table in the same or separate spreadsheet.
We have a short table with only two rows to sort. Hence, we will create our new table in the same sheet. Refer to the following image.
Make sure to use similar headers as above to avoid future confusion while you frequently refer to the dataset.
Step #2 – Sort data Horizontally using the SORT function
Select the first cell beside the first-row header, which in this case is the Department
- Type “=transpose”
- Click on the first option or press “Tab” on your keyboard
- Next, type “sort”, to select the SORT function
- Press “Tab” on your keyboard or click on the first option
- Again, type “transpose” and press “Tab” on your keyboard or select the first option to choose the Transpose function
- Select the table range and make sure you do not include headers, as shown below
- Close the bracket for Transpose that we used for table range by typing “)”
- Press “,” on your keyboard to move to the next argument of the SORT function
- Put “1” as we plan to sort the Department row
- Press “,” to move to the next argument
- To sort the Department columns in ascending order, we will type “true.”
(Note that you can type “false” in case you wish to sort the data in descending order) - Close the bracket for the SORT function using “)”
- Now, we also need to close the bracket for the Transpose function we are using to flip the sorting results by typing “)”
- Press “Enter” on your keyboard
Boom!
Your horizontally sorted table is ready.
Overall, the steps are similar and easy to follow if you use the Transpose function without error. Make sure to close the brackets at the end using the “)” twice.
Final Words
Data sorting and filtering are standard for Google Sheets and Microsoft Excel spreadsheets. It makes data analysis easier and more accessible.
Overall, the SORT function is an incredibly useful method for sorting single or multiple columns.
The main benefit of using the SORT function is it creates a dynamic table that updates automatically considering the changes and additions you make to the primary dataset.
Using the SORT function to sort the data in a custom order is possible. There is an advanced related variation of the SORT function called “SORTN”, which is helpful in this case.
I hope this article taught you all the bells and whistles of using the SORT function to sort the data in ascending and descending order.
Feel free to comment below if you have any doubts or are stuck somewhere while using the SORT function for single or multiple columns. I will try to answer them as soon as possible.