When it comes to sorting by the day of the week in Google Sheets, we can use either a custom sort order or the QUERY function.

The method you choose depends on how your data is structured and what you want to achieve.

Not sure which method to use?

I’ll explain the scenarios where each approach works best.

When to Use Custom Sort Order

If you simply need to sort a list in Google Sheets by the day of the week, a custom sort order is the way to go.

When to Use QUERY

If you need to group data first and then sort by the day of the week chronologically (not alphabetically), the QUERY function is the best option. However, by default, QUERY sorts weekdays from Sunday to Saturday.

If you want the order to be Monday to Sunday, you can use a combination of the custom sort order and the SUMIF function.

In this Google Sheets tutorial, let’s explore both methods in detail.

Sort by Day of Week Using Custom Sort Order

Consider the following dataset in B2:C8:

To sort this data by the day of the week (Monday–Sunday), use the following formula in E2:

=SORT(B2:C8, MATCH(B2:B8, {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"}, FALSE), TRUE)

Formula Explanation:

• The MATCH function assigns a numerical order to each weekday.
• The SORT function sorts the data based on that order.

If you prefer the order to be Sunday–Saturday, simply adjust the array inside the MATCH function.

Sort by Day of Week in Chronological Order Using QUERY

If your goal is to group and then sort by the day of the week in Google Sheets, the QUERY function is ideal.

Here’s an example dataset in A2:B (Date | Value):

Use this QUERY formula in I2:

=QUERY(A2:B, "SELECT DAYOFWEEK(Col1), SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY DAYOFWEEK(Col1) LABEL DAYOFWEEK(Col1) '', SUM(Col2) '' FORMAT DAYOFWEEK(Col1) 'DDDD'")

Why Does This Return Sunday-Saturday Order?

• The DAYOFWEEK function returns numbers 1–7, where 1 = Sunday, 2 = Monday, etc.
• Unlike the WEEKDAY function, QUERY does not allow you to specify an alternative starting day.
• The default sorting order in QUERY is Sunday–Saturday.

Custom Sort Order with SUMIF for Monday-Sunday Order

If you prefer a Monday–Sunday order when grouping, follow these steps:

1. Create a Helper Column (C2:C) with this formula in cell C2:

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(A2:A, "DDDD"), ""))

2. Define the Custom Order in F2:F8:

={"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}

3. Apply SUMIF for Grouping and Sorting in G2:

=ARRAYFORMULA(SUMIF(C2:C, F2:F8, B2:B))

Additional Tips:

• To remove the helper column (C2:C), integrate its formula directly into SUMIF:

=ARRAYFORMULA(SUMIF(IF(LEN(A2:A), TEXT(A2:A, "DDDD"), ""), F2:F8, B2:B))

• Modify the values in F2:F8 to adjust the sorting order as needed.

By following these methods, you can sort by the day of the week in Google Sheets efficiently, whether you’re working with simple lists or grouped datasets.

Related Resources:

• Convert Weekday Name to Weekday Number in Google Sheets
• Compare Same Day Last Week in Google Sheets
• Autofill Days of the Week in Google Sheets
• Calculate Days Between Weekday Names in Excel and Google Sheets