Sorting by month name might be familiar to Excel users, as Excel offers a custom sort option. However, Google Sheets handles this differently. To sort by month name in Google Sheets, you need to use a custom formula.
I have a list in Google Sheets with a column of month names in text format. Unfortunately, this column does not sort in chronological order. When I apply Data > Sort range after selecting it, the months are arranged in alphabetical order.
You can sort month names in chronological order in Google Sheets using either the SORT or SORTN function. In this tutorial, I will demonstrate how to use the SORT function to achieve this.
By default, the SORT menu or formula in Google Sheets allows you to sort your list in ascending (A-Z) or descending (Z-A) order.
Let me show you how to adjust the SORT function to sort a column containing month names in text format chronologically.
Examples of Sorting by Month Name in Google Sheets
If the month names are in column A within the range A2:A13, you can use the following formula in cell B2 to sort them in chronological order:
=SORT(A2:A13, MONTH(A2:A13&1), 1)
How does this formula sort month names in chronological order?
When you append the number 1 to a month name (e.g., “January1”), it is interpreted as a date (e.g., 01/01/2024), where the year defaults to the current year.
In this formula, we append 1 to the month names to convert them into the first day of each month in the current year.
The MONTH function, one of the date functions, then extracts the month numbers from these dates.
Although ARRAYFORMULA is typically required for operations involving arrays, it is not necessary here because the SORT function itself operates as an array formula.
The SORT function then sorts the month names in chronological order based on the month numbers.
Here’s the generic version of the formula to sort month names in Google Sheets:
SORT(range, sort_column, is_ascending)
In this case:
range
:A2:A13
sort_column
:MONTH(A2:A13&1)
is_ascending
:1
(for ascending order) or0
(for descending order)
If you want to sort data in a range such as A2:C13, where month names are in column A, use the following formula:
=SORT(A2:C13, MONTH(A2:A13&1), 1)
Resources
- Convert Month Name to Month Number in Google Sheets
- Google Sheets Query: How to Convert Month in Number to Month Name in Text
- Convert Month Numbers to Month Names in Google Sheets
- Month Name as the Criterion in Date Column in Query Function in Google Sheets
- Convert Month Name to Days in Google Sheets
- Display Month Names Only at Month Start (Excel & Google Sheets)