In this tutorial, we’ll reveal the secrets to effortlessly sorting data by month in both Excel and Google Sheets. Whether you’re a spreadsheet novice or a seasoned pro, these techniques will help you organize your data in a way that makes sense. So, get ready to unlock the power of sorting by month!
Sorting by Month as Text
Imagine you have a column of months that you want to arrange chronologically from oldest to newest. Follow these simple steps:
- Select the range of data including the header that you want to sort (B1:B10) and in the Ribbon, go to Home > Sort and Filter > Custom Sort.
- In the Sort dialog box, under Order, choose Custom List….
- In the Custom Lists screen, select January, February, March, and so on from the list on the left, then click OK.
- Now, back in the Sort dialog box, you will see the custom list for the months under Order. Click OK.
And voilà! Your months in column B are now sorted chronologically.
Sorting Dates by Month
Now, let’s take it a step further. Imagine you have a column of dates, and you want to sort them by month, regardless of the year, from January to December. Don’t worry, we’ve got you covered!
To achieve this, you’ll need to add an auxiliary column. Follow these steps:
- In cell C2, enter the formula:
=MONTH(B2).
The MONTH function returns the month number based on a given date. In this case, the value in C2 would be 2 (representing February).
-
Drag the formula down from the bottom right of cell C2 until the end of the range (C10).
-
Now, you have a month number for each date in column B, and you can sort both columns based on column C. Select the range in column C (C1:C10), then in the Ribbon, go to Home > Sort and Filter > Sort Smallest to Largest.
- In the pop-up window, choose Expand the selection, and click OK.
As a result, both columns are sorted by the month number in column C. You can now safely delete the auxiliary column C. Your dates in column B are now sorted by month, from oldest to newest.
Discover Our AI Formula Generator
Sorting months as text in Google Sheets is slightly different from Excel, as there is no similar option for custom lists. However, fear not! You can use an array formula to achieve the same result. Try entering this formula in cell C2: =SORT(B2:B10,MONTH(B2:B10&1),1).
In this case, the MONTH function takes a range as input, instead of a single value. The formula will return an array of numbers from 1 to 12 (representing each month) sorted in ascending order. The SORT function will take the values from B2:B10 as an array and sort them accordingly in column C, from oldest to newest.
For sorting dates by month in Google Sheets, you can use the same approach of using an auxiliary column and the MONTH function as explained for Excel. The formula is almost identical to the previous step you used to sort months as text: =SORT(B2:B,MONTH(B2:B),TRUE).
The logic remains the same. The MONTH function will sort the dates in the range from oldest to newest, and the SORT function will create a new array in column C based on this list.
Ready to explore more advanced features in Excel and Google Sheets? Head over to Crawlan.com to discover a wealth of knowledge and resources!
Now you have all the secrets to sorting your data by month like a pro! Implement these techniques in your spreadsheets and organize your data effortlessly. Happy sorting!
Remember to bookmark Crawlan.com for more helpful tips and tricks. Keep rocking those spreadsheets!
