The custom sort function in Google Sheets allows data in a column to be ordered. The term “custom sort” is often used interchangeably and there are a variety of options available when sorting data in Google Sheets. In this article we will explain the various methods to perform a custom sort in Google Sheets.
Custom Sort Using the Built in Google Sheets Feature
In our example data we will custom sort the “Size” column A-Z using the built in feature in Google Sheets.
The example data set has three varieties of jackets with different sizes. We will order this data A-Z using the built in sort feature in Google Sheets.
Step 1: In Google Sheets, open your existing file or create a new data set.
Step 2: Select the range of data to custom sort > right click > select “View more cell actions” and “Sort range”.
Step 3: Choose your sorting options
After your sort range has been selected Google Sheets will present a further dialogue box. The dialogue box gives several options:
“Data has header row” - Check this box if your data range includes the header.
“Sort by”: Column - If you have included multiple columns in your selected range, this option will allow you to choose which column of data you want to custom sort. You then have the option to sort A-Z or Z-A
“Add another sort column” - If you have selected multiple columns in your data range you can use this option to add a secondary parameter to the custom sort function. This allows the option to specify the order of the columns you want to sort by first.
Finally select the Sort button to confirm, your selected data will be sorted to your custom specifications.
Custom Sort A to Z / Z to A Using The Sort Function
Below is the syntax of the SORT function and how to use it in Google Sheets:
=SORT(Range,Column,is_ascending)
Formula Breakdown:
We must first specify =SORT followed by:
Range: The range of data we wish to be included in the custom sort.
Column: the column of data you wish to sort
Is_ascending: This is a boolean value which represents a truth or false value.
"true" marks ascending / A-Z , "false" marks descending / Z-A.
Lets see below how that looks in our example:
To break this down, we can see how the A-Z custom sort function in Google Sheets operates:
Step 1:
Select an empty column next to the data set and input the function =SORT(
Step 2: Input the range followed by a comma and the column range, you can drag to select this also.
Step 3: Input the boolean value of true or false relating to ascending or descending.
Step 4: Close brackets to end the function and press enter. Your new custom data is available in the area selected.
Custom Sort Using Specified Order Parameters
A-Z may not always suit our unique sorting requirements and we will want to specify our order conditions in Google Sheets. This is performed by using the ARRAYFORMULA function.
=ARRAYFORMULA (SORT (RANGE, MATCH (COLUMN), {DATA ORDER}, is_ascending)
We can use this syntax in Google Sheets to specifically custom sort our data. This example we arrange the Size column M, L and S ascending.
Step 1: In Google Sheets, open your existing file or create a new data set.
Step 2: Select a cell adjacent to your data set, leave a cell gap for the header.
Step 3: Write the formula
=ARRAYFORMULA(SORT(RANGE,MATCH(COLUMN,{DATA ORDER},),is_ascending))
Formula Breakdown:
=ARRAYFORMULA
This specifies that we are going to perform multiple calculations on various items in the array,
MATCH
Returns the relative position of an item in an array or range of cells.
RANGE
The range of data we are wishing to sort.
DATA ORDER
This data is the parameters in which we are specifying the list to be ordered in. If we apply this to our example we would use "M","L","S" which relate to the sizes in our example data set above.
Is_ascending
This is the boolean declaration that tells the program the order to sort the aforementioned list. By setting the value to “true” we are stating the list is ascending, whereas “false” is stating a descending list.
Press enter, we can now see the data in the size column is sorted as per our specifications.
Custom Sort Using Multiple Columns
We can apply the custom function to custom sort multiple columns in Google Sheets, this is an advanced use of the SORT function we used earlier.
Let us see how this custom SORT function is applied:
Step 1: Select a cell adjacent to your data set, leave a cell gap for the header.
Step 2: Write the formula:
=SORT(Range, Column 1, is_ascending, Column 2, is ascending)
Formula Breakdown:
We must first specify =SORT followed by:
Range:
The range of data we wish to be included in the custom sort.
Column 1:
The column of data you wish to sort first
is_ascending:
This is a boolean value which represents a truth or false value.
true marks ascending / A-Z , false marks descending / Z-A.
Column 2:
The column of data you wish to sort second
is_ascending:
This is the true/false value representing the order of Column 2
Step 3: Press enter and the data in both columns is sorted as per our specifications.
Lets see below how that looks in our example:
Recommended articles
More resources
