How to Custom Sort in Google Sheets

There is already a sorting option available in Google Sheets. But sometimes, it happens that I want to perform custom sorting based on an order I want. The problem is that you can sort based on numbers and alphabets in Google Sheets, but there’s no default option for custom sorting.

In this case, if you want to do a custom sort, you need to follow some simple steps, which I will share with you in the tutorial.

In our example, we have a date for students based on their age and height. We need to sort this data to get 14 and 15 at the top, 10, 11, 12, 13 after that, and 16 and 17,18 at the end.

Custom Sort in Google Sheets with Custom Order Helper Column

To sort the data in Google Sheets, use a custom order for the age column, specifically with ages 14 and 15 at the top, followed by ages 10 to 13, and then ages 16 to 18 at the bottom; you need to create a custom column that can create a helper column.

Next, you need to use the VLOOKUP to get this custom index number to the name data so we can use it as a sorting order.

In Column E, next to each student entry, use the VLOOKUP function to look up the age in the custom sort order list and return the corresponding index. Age is in column B, and your age-index table is in F1:G10; you need to write this formula in cell E2:

From here, you need to use the newly added Index column to sort the data how you want. So, for this, select your data, including the newly created index column.

Go to the Data menu and choose Sort range, then go to the Advanced range sorting options.

Check the option “Data has header row” in the sorting dialog. Now, in the “Sort by”, select the index column we have just created. Set the order from A to Z.

Now, there is one thing you need to understand here: when you create a custom sort index column, it sorts the data using that sort order. But there is still some improvement we need to do.

If you look at the data above, the age column is still not sorted properly, though the entire data is sorted in the way we want. 14 and 15 are at the top, then 10, 11, 13, and in the end, 16, 17, and 18.

To fix this problem, you need to perform multiple-column sorting with the custom sort. For this, again, go to the data tab and open “Advanced range sorting options.” In the dialog box, you need to add two columns to sort the data, Index and Age.

Then, the moment you click OK, it sorts the data using the custom sort order from the Index columns and then using the age column.

Note – After sorting, you can hide or remove the index column if it is no longer required or you don’t need to sort data again.

Use the SORT Function to Custom Sort the Data

Let’s say you want to sort the data in a custom order but don’t want to sort the original data. For this, you can use the SORT Function.

The SORT function can sort data in a specified range by one or more columns in ascending (TRUE) or descending (FALSE) order. It rearranges all rows in the range based on the values from a column you specify.

=SORT(A2:E21,5,TRUE)

The formula sorts all rows within the range A2:E21 based on the values in column E, which is our index column. All the rows are sorted according to the values in the index column, but the rows remain intact.

It’d be better to use a multiple-column sort instead of a single-column custom sort in this method. So, you need to use a formula with two-level sorting instead of one.

=SORT(A2:E21,5,TRUE,2,TRUE)

This sorts the data based on two columns, the first is column E, which is the custom index column, and the second one is column B, which is the age column.

Note– Using the above formula, ensure no merged cells are in the index column.

Use a Google App Script for Custom Sort in Google Sheets

Here, we have a code that uses a custom sort order, which we have already defined in the code. With this code, you don’t need to add an index column in the data, but, as I said, you do need to add the sort order within the code.

function customSortDataByAge() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A2:D21'); // Defines the range from A2 to D21
  var data = range.getValues();

  // Define the custom sort order with groups
  var sortOrder = {
    14: 1,
    15: 1,
    10: 2,
    11: 2,
    12: 2,
    13: 2,
    16: 3,
    17: 3,
    18: 3
  };

  // Sort data based on custom sort order and sub-sort by age within the same group
  data.sort(function(a, b) {
    var groupA = sortOrder[a[1]] || 4; // Default group value 4 if age not found in sortOrder
    var groupB = sortOrder[b[1]] || 4;
    if (groupA === groupB) {
      return a[1] - b[1]; // If in the same group, sort by age itself
    }
    return groupA - groupB;
  });

  // Set the sorted data back to the sheet
  range.setValues(data);

  // Optionally, log ages to the console for verification
  data.forEach(function(row) {
    console.log(row[1]); // Logging the age from Column B
  });
}

You can change the range within this code from the “range” variable. You can also change the sort order from the “sortOrder” variable. To change the column number, you need to edit it from the groupA and groupB variables.

To run this code, go to Extension > App and paste the code into the code editor window. After that, save the code by clicking on the save button and then click on the Run button to run the code.