Custom Sort Order in Google Sheets QUERY

Published on

By default, Google Sheets’ QUERY function does not support defining a custom sort order using the ORDER BY clause. However, there is a workaround! You can first process the data using QUERY and then apply custom sorting with the SORT function. This can all be combined into a single formula using the LET function for convenience.

What Is a Custom Sort Order?

A custom sort order allows you to sort a column in a specific order rather than the default alphabetical or sequential order.

If you’ve processed data using QUERY and want to sort a specific column in a custom order, this approach will help.

Generic Formula

=LET(
   qry, your_query_formula, 
   order, ARRAYFORMULA(XMATCH(CHOOSECOLS(qry, sort_column), custom_order)), 
   SORT(qry, order, 1)
)

Formula Explanation:

  • your_query_formula – Your QUERY formula (without custom sorting).
  • sort_column – The column index within the QUERY output that you want to sort.
  • custom_order – An array or range containing the unique values arranged in your preferred order.

Example: Applying Custom Sort Order in QUERY

Below is a sample dataset containing Employee ID, Name, Department, and Salary:

Sample employee data for custom department sorting

Basic QUERY Sorting (Alphabetical Order)

The following QUERY formula selects all rows (excluding empty ones) and sorts by the third column (Department) in ascending order:

=QUERY(A1:D, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC")

This results in Finance appearing first, followed by HR and IT (alphabetical order).

Applying a Custom Sort Order

Now, let’s say you want the Department column to be sorted in this custom order: HR → Finance → IT

Here’s how to modify the QUERY formula to apply a custom sort order:

=LET(
   qry, QUERY(A1:D, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC"), 
   order, ARRAYFORMULA(XMATCH(CHOOSECOLS(qry, 3), {"Department"; "HR"; "Finance"; "IT"})), 
   SORT(qry, order, 1)
)
Example of a custom sort order in Google Sheets QUERY

Breakdown of the Formula:

  • QUERY(A1:D, "SELECT * WHERE Col1 IS NOT NULL"): Fetches data, excluding empty rows.
  • CHOOSECOLS(qry, 3): Extracts the third column (Department) from the QUERY output.
  • XMATCH(CHOOSECOLS(qry, 3), {"Department"; "HR"; "Finance"; "IT"}): Assigns a custom sorting order.
  • SORT(qry, order, 1): Sorts the QUERY results based on the custom order.

Alternative Approach Using Cell References

Instead of manually typing the custom order in the formula, you can enter the sorting values in E1:E4 as follows:

E
Department
HR
Finance
IT

Now, update the formula to use the range instead:

=LET(
   qry, QUERY(A1:D, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC"), 
   order, ARRAYFORMULA(XMATCH(CHOOSECOLS(qry, 3), E1:E4)), 
   SORT(qry, order, 1)
)

This makes it easier to update the sort order dynamically.

FAQs

1. What happens if the custom order does not match all values?

If all values in the column do not match the custom order, the QUERY result remains unchanged. If some values match, they will be sorted as per the custom order, while unmatched values appear at the bottom in their default order.

2. What if my QUERY result does not have a header row?

In that case, exclude the header from the custom order array/range.

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.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

4 COMMENTS

  1. How did you avoid the following Error Message by not including Col3 in the “Select” portion of your query?

    "Error COL_IN_ORDER_MUST_BE_IN_SELECT: 'Col3'"

  2. Awesome, That’s brilliant.

    Just for completeness, why does the ArrayFormula need to be moved to the outside, and what happens if it isn’t placed there?

    Thank again – This should allow ordering output by weekdays in proper order!

    • Hi, Stu,

      It won’t make any difference in this case. But make it a practice.

      If you have two or more ArrayFormulas in a combination formula, you can remove all that by placing only one ArrayFormula in the front.

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.