Formula for Sorting by Month Name in Google Sheets

Published on

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.

The role of the MONTH function in sorting month names

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) or 0 (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)
Example of sorting month names in chronological order

Resources

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

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.