How to Sort by Month (Dates) in Excel

puneet-gogia-excel-champs

- Written by Puneet

In Excel, if you want to sort dates using the months, the best way is to create a helper column for the month numbers and then sort data based on those. And in this tutorial, we will learn the method in detail.

sort-by-month

Let’s get started.

Steps to Sort Dates by Month

  1. First, in the second column (Column B) create a heading with the name “Month”.
    2-create-a-month-column
  2. In cell B2, enter the Month function and refer to cell A2, where you have the data. Once you enter, hit enter to get the result.
    3-enter-month-function
  3. After that, drag down the formula up to the last cell of the column. You can double-click on the small green square from the bottom right of the cell. This will drag the formula up to the last cell.
    4-dragdown-the-formula
  4. From here, you need to open the sorting option. Select the entire data and use the keyboard shortcut (Alt > A > S > S), or you can also go to Data > Sort & Filter > Sort to open the Sort dialog box.
    5-open-sorting-option
  5. Next, from the “Sort by” drop down, select the “Month” column. And from the “Order” select “Smallest to Largest”.
    6-sort-by-month-column
  6. In the end, click OK to apply the sorting.

When you click OK, it sorts the data using the month column. And your dates get sorted by months starting from January to February.

data-sorted-by-month

Using Month Names Instead of Numbers

Instead of using the month number, you can also use the Month names and then sort the date based on those month names. In the month column, enter the TEXT function to get the month name from the date.

text-function-to-sort-months
=TEXT(A2,"MMMM")

From here, the next thing is to select the data and sort it using the month column. So, select the data and then use the keyboard shortcut (Alt > A > S > S) to open the SORT dialog box. Select the “Month” column from the “Sort By” in the SORT dialogue box.

sort-by-month

After that, from the “Order” dialog box, select the “Custom List”.

order-dialog-box

Selecting “Custom List…” will open a new dialog box from where you need to select the custom list of months to sort the data.

select-custom-list

Note: If you don’t have the list of month names, you can learn to create it from this quick guide.

Finally, click OK to go back to the SORT dialog box and again OK to apply the sorting. And once you click OK, it sorts the data based on your month names in the month column.

sorted-data-by-month

Get the Excel File

Last Updated: April 28, 2024