Today, we want to learn about sorting by months with the SORT function.
SORT by months using functions
Let’s start with writing the SORT function, with an array as the first argument. In the second argument we need to write 2, as we want to sort by months, which are located in the second column. Now, we can close the function (Fig. 1).
=SORT(A2:C145,2)
Fig. 1 SORT function
Just like that the SORT function sorted data by the Month column. It’s important that the SORT function doesn’t consider the custom list. Excel does it, but the SORT function doesn’t. Now. let’s have a look at how we can properly sort by months using the SORT function. First of all, we cannot use the SORT function, but the SORTBY function. In this function, even if we change the second argument to a month column, we still get an ascending sorting, from A to Z. It means that the sorting is based on the alphabet, not on months of the year. The thing is we cannot do the sorting based on arguments. We need to use the MATCH function. It will look up a given month in a list of months. In the third argument we write 0, as we want to have the exact match (Fig. 2)
=MATCH(B2,$J$2:$J$13,0)
Fig. 2 MATCH function
The MATCH function starts the sorting from the top on the Month column. As we can see in column D, we have a proper number for each month. The column acts as a helper column, however, sometimes we don’t want any helper column, so we can create a helper column in our formula. We can match each month from the Month column (column B) using the MATCH function, where we need to write the lookup value in the first argument, which is our data column. In the second argument, we need to write the whole list of months on the right, then let’s write 0 as the exact match (Fig. 3)
SORTBY(A2:C145,MATCH(B2:B145,J2:J13,0))
Fig. 3 SORTBY function
It works. We see that the SORTBY function found a proper numbers for each month. It’s important that it sorts by months, not by years. However, we can add the year as well. But, I want to show you something else. First of all, if we don’t like the custom helper column list, we can just click F9 in the lookup array argument to hard code the array list in our formula (Fig. 4)
We can even delete the custom column. As we can see, it works properly. When we look at column D, we can see #N/A error. It’s because the MATCH function is trying to find months in the custom column cells which are empty (Fig. 5)
=MATCH(B2,$J$2:$J$13,0)
Fig. 5 Empty cells in the Month column
Now, let’s focus on dates based on years and months. Sometimes, our date in a dataset isn’t written only in one column, but it can be separated into the year, month and day columns. We can create a full date from the data we have. We will try it in the Helper column. First of all, we write =, then 1 combined with a month, combined with a year (Fig. 6)
=1&B2&A2
Fig. 6 Creating a date
And just like that, we combined the 1st of January 2022. Our date, however, is text. We want Excel to understand it as a date, so we just need to add parentheses and a 0 (Fig. 7)
=(1&B2&A2)+0
Fig. 7 Changing text into numbers
Excel magic has just happened! It changed text into numbers. Now, to change the numbers into dates, we need to go to the Home tab (1), then change the formatting (2) into Short Date (3) (Fig. 8)
Fig. 8 Numbers into a date
Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY function. In the first argument we write the data column, and in the second column we have to combine 1 with the Month column, then with the Year column. When we close the parentheses we need to add 0 to change text values into numbers (Fig. 9)
=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)
Fig. 9 SORTBY function
Now, we have only six Januaries at the beginning, because we have have only six Januaries in 2022. Next six Januaries are in 2023. This way we sorted our data by months and years using a helper column.
Today, we want to learn how the SORT function works in Excel.
Excel SORT Function
The SORT function is available from Dynamic Array Excel, which is around 2021. Let’s start with the simplest version of the SORT function, where we only need to write an array. We have to remember that the array must be given without headers. Let’s select our data and put it as an argument and let’s put our formula into a cell (Fig. 1)
=SORT(A2:C169)
Fig. 1 SORT function
And we have the results. The most important thing about formulas in Dynamic Array Excel is that they spill. It means that our formula is in only one cell, but the results spill. The formula is only in cell E2, but when we click on cell E3, we can see that the formula is grayed out in the formula bar. It means that it contains results of this function, but doesn’t contain the function itself (Fig. 2)
Fig. 2 Formula grayed out
When we add an array with data to be sorted, Excel’s sorting will be based on the first column, which is the column with dates. In the sorted array we don’t see dates but numbers because Excel doesn’t know how to copy the formatting in array formulas. If we want to have proper formatting, we can copy the formatting using the Format Painter (2) from the Home tab (1). We will also highlight the cell with the formula (3) (Fig. 3)
Fig. 3 Using the Format Painter command
Now, our results are formatted. Since we sorted by the first column, our results are basically the same as in our original column. To change that, we can add the second argument to our SORT function, which is the sort index. It’s the number of a column by which we want to sort our data. Let’s write 2, meaning the second column (Fig. 4)
=SORT(A2:C169,2)
Fig. 4 SORT formula with the second argument
We can see that the sorting has changed. Now, we can see that only Chandlers are at the top because it’s an ascending sorting. This way we can sort by any column. Let’s move on to the third argument, which is the sort order. Let’s change it to a descending sorting by writing ‑1 (Fig. 5)
=SORT(A2:C169,2,-1)
Fig. 5 Descending sorting
Now, the first salesman in Ross. Let’s add something more. Let’s sort by Sales from the largest to the smallest. Now, the sales are written randomly in the Sales column. We can add the third column to the second argument. We can also add a reference to cells where numbers of columns are written. It’s very important to write them in the correct order. In our case the first cell from the top contains 2 and the second one contains 3. It means that first, we will sort by the second column, and then by the third column (Fig. 6)
Fig. 6 Adding a cell reference
And we have the results. We can see that Ross is sorted by values in the Sales column. The same is with the rest of the salesmen. Now, let’s focus on the third argument once again. We have two columns of sorting in the second argument, but only one number (-1) in the third argument which is the sort order. It means that the sorting is descending for each column. When we want to have an ascending order for one column and a descending order for another column, we can do the same as we did with the second argument. We have to refer to some cells in Excel. In our case we have a sorting order for each column (Fig. 7)
Fig. 7 Referring to other cells
Now, we have an ascending sorting for the Salesman column, and a descending sorting for the Sales column.
When we don’t want any additional cells in our sheet, we can hard code them in the formula by pressing F9 key. It changes the argument into an array. We can do the same with the sort order argument. When we look at the formula now, we can see that the second column in ascending, and the third column is descending (Fig. 8)
Fig. 8 Hard coding the values
Now that we have everything we needed, we can cancel the unnecessary cells. We don’t even need the third argument, as it is reserved for horizontal sorting. Here, we are sorting only by columns, so we can leave it like that. Here are our results (Fig. 9)
