Making use of the SORT function can allow Google Sheets to automatically sort your data. Using this formula, you can create a dynamic table that automatically updates whenever you change something in the primary table.
That being said,
Manual sorting is not feasible if you keep adding new information to your table frequently.
You’re at the right place if you are looking for a solution where you add new records and have them automatically sort in Google Sheets.
In this article, we will learn how to auto sort data by the values in one or multiple columns using the SORT function in Google Sheets.
Explained: SORT function in Google Sheets
The Sort function is a simple array formula with the following syntax.
=SORT(range, sort_column, is_ascending)
Here is a quick explanation of each argument from the above formula,
- “range” is the table that needs to be sorted
- “sort_column” is the exact column you wish to sort
- “is_ascending” arguments let you decide if you wish to sort in ascending or descending order
Download the Example Spreadsheet
If you have your own Google Sheet ready to practice the Sort function, you can skip this section.
However, we suggest downloading the example Google Sheet with the data used to demonstrate the Sort function in this article for better understanding.
How do you automatically sort in Google Sheets using the SORT function?
Now, let’s learn how to use the Sort function in Google Sheets,
Step #1 – Create A New Table
Note that you will create a new dynamic table other than the primary dataset.
The dynamic table will be updated automatically whenever you add new records to the primary dataset.
You can create this new dynamic table within the same or a new spreadsheet.
For the sake of this article, I will use the same spreadsheet.
- Open the Google Sheet here.
- Go to the worksheet containing the data table to be sorted
- Create a new table
- Add headers to the table.
PRECAUTION: The most common mistake while using the Sort function
You must ensure that all the new table’s cells are empty. Otherwise, Google Sheets will return an error, and the Sort function will not work.
For example, look at the following image,
The new table contains a false entry “asd*” in cell E10. Due to this entry, applying the Sort function returns the error “Array result was not expanded because it would overwrite data in E10.”
Step #2 – Sort the table using the SORT function
It is the main step in using the Sort function. You can either manually enter this formula or select it from the main menu, as shown below,
I know most of you may prefer manually typing the formula by clicking on the respective cell. So, we’ll also do the same while sorting the data.
- Click on the cell below the first header, “Full Name.”
- Type “=SORT”
- Select the first option from the popup or press the “Tab” on your keyboard
- Now, go ahead and select the table, and make sure you only include the main data and not the headers
- Press “,” on your keyboard to move to the next argument
- Enter the column number which you wish to sort. In this example, we have selected the second column by putting “2.”
- Press “,” on your keyboard to move to the next argument
- Type “TRUE,” as we are about to sort by date in ascending order
- Next, you need to close the brackets by typing in “)”
- Press “Enter” on your keyboard
This will instantly create a new table that you can use for further analysis. The newly created dynamic table will be updated automatically whenever you change or add something to the main table.
Q. How does automatic sorting work using the SORT function?
Refer to the following image, where we have employee names in one column and their department in the next.
After using the Sort function in the same spreadsheet and sorting the table by department column, the final output will be as follows:
Now, let us add one new record for Adam Park, who works in the Accounting department.
Boom! It works like a charm.
Hmmm! What if automatic sorting didn’t work for you?
The automatic sorting works flawlessly if you change or adjust something in the cells of the existing table.
On the other hand, things will get complicated when you add new entries, e.g., adding new rows to the table like we did earlier.
Instead of selecting a limited number of rows from the table’s column, select the entire column and include all the rows.
Here’s how you can do that,
You need to use the shortcut on your keyboard, “CTRL + SHIFT + Down Arrow,” and use it twice, as shown in the above GIF.
This adjustment will make sure that any information added in that column in the future will reflect in the newly created dynamic table using the Sort function.
As mentioned earlier, the Sort function is an array formula. You must avoid making any changes to the newly created table. It will make your dynamic table disappear until and unless you delete the change or adjustment.
The Sort function is prebuilt in Google Sheets, making it easier and more convenient. We suggest using the Sort function most of the time.
However, a couple of Add-ons for Google Sheets get this job done for you, but you need an extra workaround.
I hope you learned all the bells and whistles of automatic sorting in Google Sheets.
In case you are stuck somewhere or still have any doubts, feel free to comment below. I will answer all of your questions as soon as possible.
Other Related Google Sheets Tutorials
- How to Filter With Custom Formula In Google Sheets
- How to Sort by Last Name in Google Sheets
- How to Sort Alphabetically in Google Sheets
- How to Sort by Number in Google Sheets (Sort by Value)
- How to Custom Sort in Google Sheets
- How to Sort Rows In Google Sheets
- How to Remove Filters in Google Sheets
- How to Create & Use a Filter View in Google Sheets