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

We can use a combination formula rather than the SORT command in Google Sheets to sort data by custom order. In Excel, we can use either a formula or the SORT command, as explained in this Excel Guide: Custom Sort in Excel (Using Command and Formula).

The SORT function in Google Sheets allows you to sort your data in ascending or descending order. You can specify the sort by column (containing the values by which to sort) as either a column index or a range reference.

If the sort by column is outside the sort range, it’s not possible to specify the column index. In such cases, we will specify the range reference. We can leverage this peculiarity of the function to sort data by custom order in Google Sheets.

Syntax:

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

Arguments:

  • range: The source data range.
  • sort_column: The column index number or range reference indicating the column by which to sort. For example, you can use =SORT(A1:C10, 3, TRUE) or =SORT(A1:C10, C1:C10, TRUE) to sort the data range A1:C10 by the third column in ascending order. A range specified as a sort_column must be a single column with the same number of rows as the range.
  • is_ascending: Indicating the sort order – TRUE for ascending or FALSE for descending.
  • sort_column2, is_ascending2 …: [OPTIONAL] Additional columns and sort order.

As a side note, custom sorting is useful for sorting data by the status of tasks (pending, completed, in progress), types of cheques (PDC or CDC), hierarchy in an organization, and so on.

To sort by custom order, in addition to the SORT function, we may need to use one of the functions MATCH, XMATCH, or SWITCH.

Sorting Data by Custom Order in Google Sheets

Here is an example of sorting data by custom order in Google Sheets where A2:C8 contains customer names, cheque amounts, and modes of payment in columns A, B, and C respectively.

The modes of payment are “Pending,” “CDC” (current-dated cheque), and “PDC” (post-dated cheque). I want to sort the data in A2:C8 based on the values in C2:C8 in the custom order: “Pending,” “CDC,” and “PDC.”

Custom Sort Formula that Uses SORT and MATCH:

=SORT(A2:C10, MATCH(C2:C10, VSTACK("Pending", "CDC", "PDC"), FALSE), TRUE)
Example of sorting data by custom order in Google Sheets

If you prefer to use XMATCH:

=SORT(A2:C10, XMATCH(C2:C10, VSTACK("Pending", "CDC", "PDC")), TRUE)

If you prefer to use SWITCH:

=SORT(A2:C10, SWITCH(C2:C10, "Pending", 1, "CDC", 2, "PDC", 3), TRUE)

Formula Explanation

In the above custom sort formulas:

  • range: A2:C10
  • sort_column: MATCH(C2:C10, VSTACK("Pending", "CDC", "PDC"), FALSE) or XMATCH(C2:C10, VSTACK("Pending", "CDC", "PDC")) or SWITCH(C2:C10, "Pending", 1, "CDC", 2, "PDC", 3)
  • is_ascending: TRUE

So, the key to understanding the formula that sorts data by custom order in Google Sheets is the MATCH, XMATCH, or SWITCH formulas.

MATCH:

MATCH(C2:C10, VSTACK("Pending", "CDC", "PDC"), FALSE)

Syntax:

MATCH(search_key, range, [search_type])

The formula matches values in cell range C2:C10 (search_key) within a column that contains “Pending,” “CDC,” and “PDC” returned by VSTACK (range) and returns their relative positions.

The FALSE in MATCH represents an exact match of the search_key.

In short, it will assign the number 1 to “Pending,” 2 to “CDC,” and 3 to “PDC” for the values in the third column of the sort range.

The role of MATCH in custom sort order explained

When using XMATCH, you can omit the FALSE (which is equivalent to 0), as the default value is 0 (it won’t cause any issues if you keep FALSE).

Syntax:

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

When using the SWITCH function, it tests cases in the expression and assigns the corresponding values.

SWITCH(C2:C10, "Pending", 1, "CDC", 2, "PDC", 3)

Syntax:

SWITCH(expression, case1, value1, [case2_or_default, …], [value2, …])

Sort By Custom Order and Assigning Header Row

As you can see, our data is in A1:C10, where A1:C1 contains the field labels. We omitted the header row when sorting the range as it might cause issues.

The functions that can recognize the field labels in Google Sheets are database functions and QUERY. The SORT function cannot recognize field labels.

So, to sort by custom order and assign the header row, use the VSTACK function as per the below generic formula.

Generic Formula: VSTACK(header_row, custom_sort_formula)

So, the formula will be:

=VSTACK(A1:C1, SORT(A2:C10, MATCH(C2:C10, VSTACK("Pending", "CDC", "PDC"), FALSE), TRUE))

Resources

In addition to the SORT function, we can also utilize the QUERY function to sort a range by custom order. Please check out the resources below:

  1. Custom Sort Order in Google Sheets Query [Workaround]
  2. How to Sort Pivot Table Columns in the Custom Order in Google Sheets
  3. How to Custom Sort By Partial Match in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. So this creates a new view, is that expected or is there a way to apply a custom sort to the actual data? I just created a new tab with the sorted view but was hoping to be able to just sort my data when I want to.

    Thanks for the good tips!

  2. You are a legend. Took me a few hours before I got to your post. Thanks!

    PS: Custom sort in GS still sucks compared to excel. 🙂

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.