How to Custom Sort in Google Sheets (Best Practice)

Key Takeaways: How to Custom Sort in Google Sheets

To Custom Sort in Google Sheets

Add a new column (for clean data).
Enter the custom sort formula: =ARRAYFORMULA(SORT(A2:B12, MATCH(B2:B12, {“finishing”, “washing”, “others”}, 0), TRUE)).
Press Enter.

In today’s article, we’ll see how to custom sort in google sheets.

So, as we already have seen many sort functions, now we will see something more advanced which is custom sort, custom sort means making a customized conditional sorting function, that performs sort function based on the written condition. It is a new feature to google sheets and is highly adapted to avoid traditional static sorting methods. So custom sort can be used for sorting by any means, we can sort based on any condition and conditions can be customized too. So let’s get into the basics and then to the execution.

Custom sort work as a formula, we need to pass some arguments and our sort function inside an array function acts accordingly, don’t get confused with these terms if they are new to you. We will understand this deeply in step-by-step procedure.

If you want to learn about how to sort automatically in google sheets, please follow the link below.

How to Automatically Sort Dataset in Google Sheets

Table of Contents

Why do we need to learn – how to custom sort in google sheets?

Custom sort is the solution for the limited functionalities of all the sorting functions that are pre-defined in google sheets, let’s say we want to sort alphabetically so we will use A-Z sort. We want to sort by color then we will use sort by the color filter, and similarly all sorting functions are used for some function with their limited functionalities, like we can’t use alphabetic sort for sorting concerning cell colors.

Similarly, all other sorting functions can’t be used interchangeably, because they all have a static function that can’t be customized and can only work on its limited scope. But custom sort can be customized, this is all about it, a sorting function that can be customized and programmed according to required conditions. Custom sort takes string, Boolean, and cell addresses, as arguments and generates a custom sort function according to our need, and it can be customized each time when a requirement changes. We need to learn custom sort so we can sort any range concerning anything.

To sort the data concerning our custom requirement
To avoid built-in sorting functions with customized sorting functions

Download/Copy Practice Workbook

How to Do Custom Sort in Google Sheets

In the below dataset example, we have 2 columns (Operations and Department). We would like to sort the dataset by Department name. This sorting will be applied with custom Department name String. In this example we would like to sort the dataset using the chronology Finishing, Washing and Others. Follow the steps below to learn how we can perform this custom sorting easily in Google Sheets.

Step 1

Open google sheets, open your file, or make a random data set.

Step 2

Skip a column (only for keeping data clean), and from the next column, start writing the formula.

=ARRAYFORMULA(SORT(A2:B12,MATCH(B2:B12,{“finishing”;“washing”;“others”},0),true))

=ARRAYFORMULA

Sort(Range:A2:B12

Match(Range: B2:B12

Defining Strings in double quotes inside curly braces {}

=ARRAYFORMULA{SORT(A2:B12},MATCH(B2:B12,{“finishing”,”washing”;”others”}}

Now 0 for non-repeat function

True for the argument “Is ascending”?

=ARRAYFORMULA(SORT(A2:B12,MATCH(B2:B12,{“finishing”;“washing”;“others”},0),true))

Formula explanation

Arrayformula is the name, then inside the square bracket, we have a sort that indicates it is a sorting function, then we select our entire data set, match refers to the column that contains the value to match, like in the screenshot. then we write the words which are needed to be sorted, these words are strings that are written in single or double quotes with a semi-colon as a separator. After that 0 means no-repeat, and true is for the argument (is ascending)?

Step 3

Hit Enter and see your that range is sorted concerning the strings you passed inside the formula. You can change the index of the strings inside the formula to change the sort sequence.

To recap, we learned how to custom sort in google sheets, we have seen an example that is simple but enough for understanding the basics of the custom sort. Now answer me, can you perform this kind of sort using any other static sort function? if yes please let me know. You can add as many strings as you want inside the formula. Always carefully write the spellings of the string because they are working on exact spellings, obviously an error will be passed if there is any type.

Notes

Take special care of spellings of the string.
Always write your strings inside double or single quotes.
Do not write your string in double and single quotes inside one formula.
To use quotes as a part of the string, use alternative quotes for formula, double when you want to use singly as a part of the string, and single when you want to use double as a part of string.

Conclusion

In this article, we learned how to custom sort in google sheets, this is a unique feature inside google sheets, and its recently added to solve sorting complexities, now we don’t need to rely on traditional sorting static methods, now we have the custom sort that can be customized and programmed accordingly. This is a quick guide and I hope you like this, for any questions, please comment below and share with experience with us. Thank you.