How to Custom Sort in Google Sheets (Best Practice)

how to custom sort in google sheets 3

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

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.

See also  How to Use COUNTIF & COUNTIFS in Google Sheets

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.

how to custom sort in google sheets 1

how to custom sort in google sheets 1.1

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”},),true))

=ARRAYFORMULA

See also  How to Resize Rows & Columns in Google Sheets

how to custom sort in google sheets 2

Sort(Range:A2:B12

how to custom sort in google sheets 2.1

Match(Range: B2:B12

how to custom sort in google sheets 2.2

how to custom sort in google sheets 2.3

Defining Strings in double quotes inside curly braces {}

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

how to custom sort in google sheets 2.3

Now 0 for non-repeat function

how to custom sort in google sheets 2.4

True for the argument “Is ascending”?

how to custom sort in google sheets 2.5

=ARRAYFORMULA(SORT(A2:B12,MATCH(B2:B12,{“finishing”;“washing”;“others”},),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.

how to custom sort in google sheets 3

 

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

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general