Sort by Custom Order in Google Sheets [How to Guide]

The SORT function in Google Sheets helps you to sort your data in ascending or descending order. You can also Sort your data based on any other column called sort column. But do you know how to sort by custom order in Google Sheets? You can learn that tip here.

Normally I use the function SORT for sorting my data. Sometimes I may want to aggregate my data together with sorting. That time I prefer to use the Query.

SORTN is the other option that gives you some additional capability with sorting. With this function, you can restrict the number of rows in a sorted output. So useful to find the Max/MIN rows in Google Sheets.

Other features of SORTN is its capability to return unique rows. In other words, it removes duplicates. See this tutorial.

Must Read: How to Apply Unique in Selected Columns in Google Sheets

Here what I want is to just sort my data but in a custom order. You can do it with the function SORT.

But the function SORT alone cannot do this. You should use either of the functions Match or Choose with SORT. Here are that rare Google Sheet tips.

The Formula to Sort by Custom Order in Google Sheets

Here is one example of Custom Sort in Google Sheets.

I’ve sorted the data in the range A2: C8 based on column C in the following custom order -Pending, CDC, and PDC.

You can see the formula in cell E2. There you can see in column G that the sort order is based on my above custom sort order.

How can we sort by custom order in Google Sheets as above? What’s the role of the function Match in SORT here?

Note: You can use the function MATCH or SWITCH together with SORT for custom sort in Google Sheets.

Formula 1: SORT and MATCH combo.

=sort(A2:C10,match(C2:C10,{“Pending”;”CDC”;”PDC”},0),true)

Formula 2: SORT and SWITCH Combo.

=sort(A2:C10,SWITCH(C2:C10,”Pending”,1,”CDC”,2,”PDC”,3),true)

You can use either of the above formulas. Let me explain these formulas that we can use to sort by custom order in Google Sheets.

Formula Explanation:

First, let me introduce you the SORT function syntax.

SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])

In this the arguments in the square brackets are optional and I haven’t used that in my example.

In my formula, the argument sort_column is actually Column C. But we want a custom sort order. So we can’t use the column C directly.

In column C the available values are PDC, Pending, and CDC. We want the custom sort order as Pending, CDC, PDC.

The Match formula generates a column with the value 1 for Pending, 2 for CDC and 3 for PDC. See that below.

See this Match Syntax for reference:

MATCH(search_key, range, [search_type])

The SWITCH function also does the same thing and for me, the SWITCH seems more simple.

=SWITCH(C2:C8,”Pending”,1,”CDC”,2,”PDC”,3)

Unlike Match, the Choose function is an array function by default. So when you are independently using this function, you can use it without an ArrayFormula.

Instead of the column C as sort column, we can use the above virtual column that generated by either of the function Choose or Match. That’s what I’ve done.

Conclusion

Please refer to the custom sorted column, i.e. column G on the first screenshot. You can see that, after sorting, the column keeps a grouping format. Similar items are grouped but based on the custom sort order. That’s all. Thanks for the stay. Enjoy!