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:

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)
)

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:

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.

Related Resources

• Sort by Custom Order in Google Sheets – Step-by-Step Guide
• How to Sort Pivot Table Columns in a Custom Order in Google Sheets
• Custom Sorting in Excel (Using Commands & Formulas)
• Sort Data by Partial Match in Google Sheets (Step-by-Step Guide)