ExcelInsider Team
Auto sorting in Google Sheets is essential for keeping your data organised, ensuring any new entries made to the dataset are automatically sorted according to your desired order. This feature is particularly helpful for maintaining datasets containing logs, attendance sheets, sales records, etc. Google Sheets has many built-in tools that let users auto-sort their data.
To autosort data in Google Sheets, follow the steps below:
➤ Open the spreadsheet file, and select Cell A2.
➤ Then, put the formula:
=SORT(array, [sort_index], [sort_order], [by_col])
➤ Replace array with the range of cells that you want to sort.
➤ Replace [sort_index] with the column or row number within the range to sort by.
➤ Replace [sort_order] with TRUE if you want to sort in ascending order or FALSE if you want to sort in descending order.
➤ And finally, replace [by_col] with FALSE to sort by row and TRUE to sort by column. This step is optional, and most formulas will work without needing to define it separately.
In this article, we will explore three easy methods to auto-sort data in Google Sheets using various built-in tools and functions.
Auto-Sort Data Using the SORT Function
The Sort function in Google Sheets is a useful tool that lets users sort the entire dataset automatically. The Sort function allows users to automatically sort the entire dataset in one go, saving time and improving efficiency. Moreover, if any new rows are added, they will be automatically sorted using this method.
In the given “Sales Data” dataset, we have information about Sales Person, Product Name, Monthly Sales, Region and Category in columns A, B, C, D and E, respectively. By using the SORT function, we will sort the dataset according to the ascending order of Monthly Sales in Column C. The sorted dataset will be displayed in a separate worksheet called “SORT Function Output”.
Steps:
➤ In the SORT Function Output worksheet, put the following formula in Cell A2:
=SORT('Sales Data'!A2:E, 3, TRUE)
Note:
The formula sorts the data range A2:E from the “Sales Data” sheet, which contains data on Sales Person, Product Name, Monthly Sales, Region and Category. 3 in the formula indicates that we will be sorting by the third column (Monthly Sales), and TRUE indicates we want to sort the third column in ascending order.
➤ Then, press Enter. You should now have a dataset that is sorted in ascending order of Column C.
➤ Now, when a new row is added to the “Sales Data” worksheet, we can see that the SORT function automatically updates to include the new data.
Auto-Sort Data Combining the SORT and ARRAYFORMULA Function
As discussed before, the SORT function in Google Sheets is used to automatically organize data in a specified order. On the other hand, ARRAYFORMULA is used to apply a formula across an entire range all at once. By combining these two functions, users can sort datasets easily while also retaining the ability to integrate other functions. We will sort the dataset in ascending order of Column C using the same dataset as before. A separate worksheet called “ARRAYFORMULA with SORT Output” will display the dataset sorted using this method.
Steps:
➤ In the new worksheet named “ARRAYFORMULA with SORT Output”, select Cell A2 and put the formula:
=ARRAYFORMULA(SORT('Sales Data'!A2:E, 3, TRUE))
➤ Next, press Enter, and you should have the dataset sorted in ascending order of Column C.
➤ Now, when a new row is added to the “Sales Data” worksheet, the output in the sheet “ARRAYFORMULA with SORT Output” will automatically update to reflect the new data.
Auto-Sort Data Using App Script
Google App Script is a very powerful tool that lets users automate tasks and streamline workflows. By using a custom script, users can automatically sort their dataset according to their demand. Once set up, any new entries made in the dataset will automatically be sorted without requiring manual intervention. We will again work with the same dataset and use a custom Script to auto-sort the data in ascending order of Column C. The sorted dataset will be displayed in a separate worksheet called “App Script Output”.
Steps:
➤ Open the “App Script Output” worksheet and head to Extensions >> Apps Script.
➤ Open the script editor and paste the following code:
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("App Script Output");
var lastRow = sheet.getLastRow();
var range = sheet.getRange("A2:E" + lastRow);
range.sort({column: 3, ascending: true});
}
Note:
In the custom script,
➧ function onEdit(e) is the trigger function. Meaning, this function will run automatically every time a change is made to the spreadsheet.
➧ var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“App Script Output”); It accesses the worksheet called “App Script Output” where data sorting will take place.
➧ var lastRow = sheet.getLastRow(); This line retrieves the index of the last row in the dataset, so that the script knows how many rows need to be sorted.
➧ var range = sheet.getRange(“A2:E” + lastRow); It defines the range of the data that is to be sorted. In our case, we will start from Cell A2 and end at Column E of the last data row.
➧ range.sort({column: 3, ascending: true}); It tells the script to sort the dataset based on ascending order of the 3rd column, that is Column C.
➤ You should now have a dataset that is automatically sorted according to the ascending order of Column C.
➤ To test whether the script works, we will add a new row to the dataset. We can notice that the new entry, along with the rest of the data, is automatically sorted.
Frequently Asked Questions
Why does My Header Row Move When I Sort Data?
This issue usually happens when you do not select the “Data has a header row” option from the Advanced range sorting options. Make sure to check this box to avoid this issue.
What do TRUE and FALSE mean In The SORT and ARRAYFUNCTION Function Formula?
TRUE in the formula indicates that data will be sorted in ascending order, while FALSE means the data will be sorted in descending order.
Concluding words
Auto sorting is an effective method for keeping your data clean, organized and easy to read. We have explained 3 different methods by which you can auto-sort data, including auto-sorting data using the SORT function, combining SORT with ARRAYFORMULA function and using App Script. Feel free to try out each method and select one that suits your needs.
