With any type of data, one of the best and most popular ways of organizing that data is to sort it.
Depending on the nature of the data, sorting is usually carried out based on last name, location, category, and date.
Sorting arranges the data in a way that makes it easier to find information of interest, or to detect certain patterns in the data.
Google sheets makes sorting quite easy with a number of choices available from the Data menu option, as well as through a function.
To demonstrate the sorting methods available, and specifically sorting by date, we’ll be using a sample Office Supplies Sales sheet which also includes a date column, OrderDate, shown above.
Before doing any sorting, a few words about the date type.
Date and datetime are numeric types.
However, date can be represented in varying formats, such as month / day / year, day / month / year, or year / month / day, the last being the format more commonly found in databases.
So why is format important?
Well, when you enter a date such as 07/01/2021, if you are in the U.S., you would normally interpret this as being the 1st of July 2021, whereas in Europe it would be 7th of January 2021.
It’s important therefore to be aware of which format you are using on your system, or which date format has been set in your sheet.
To do so, view the menu option Format > Numeric > Date.
In the screenshot above, in the dropdown menu we see the date 9/26/2008, where the value of 26 in the second position is a number larger than the largest possible month of 12, so must be the day part of the date, making it obvious that the format is month/day/year.
Sort by Date in Google Sheets
Having sorted that out (no pun intended), let’s now take a look at how we can go about sorting the data based on the OrderDate column.
The first method involves selecting the range of cells in the sheet.
In this example, this is all cells in the range A1 to G44.
Next, we open the menu option Data > Sort range.
At this point, we have a couple of options we can select from.
The first is Sort range by Column A (A to Z). This will work as long as our date column is the first column in the sheet, i.e. column A, which it is in our example.
We could also choose the second option, Sort range by Column A (Z to A) if wanted to sort in reverse date order (i.e. oldest to newest date).
There is one catch here, and that is that the first row is a header row.
Unless this row has been frozen or unless it has been omitted from the selected range, it will be included in the sorting and could end up anywhere, most likely as the last row since it is a text field.
The third option in the menu, Advanced range sorting options, gives us a few benefits over the more straightforward Sort range by Column A options, and in any case should be selected when the date field is not the first column.
It will open the popup dialog box shown in the screenshot above.
The first thing to note is the checkbox, Data has header row.
Selecting this will omit the header row from any sorting, if it exists and if it has been included in our selected range.
The next option of interest is the Sort by field, which allows us to select on which column the sorting should be based.
If our date column was other than Column A, we would indicate it here.
The sort order is then available from the radio buttons, A → Z and Z → A.
And finally, the Add another sort column button, which allows us to sort on more than one column.
For example, if we sorted on date, there could very likely be more than one row with the same date.
In such cases, we could then have those rows additionally sorted on a second column, such as Rep or Region.
Left mouse clicking the Sort button will now sort the data according to the options set in the dialog box.
The result is shown in the screenshot above, where we see that the data is indeed sorted on the OrderDate column.
Another way to sort the data again involves the Data menu.
However, this requires that the header row is frozen.
Also, you need to select a cell anywhere inside the column on which the sorting is to be carried out, column A in our case.
That being the case, this is a very quick method of sorting.
From the Google sheets menu, select Data > Sort sheet > Sort sheet by column A (A to Z) or Sort sheet by column A (Z to A) if you want the most recent date at the top.
The column indicated in the menu option (i.e. Sort sheet by column A) will be the column of the cell that was selected before opening the menu item.
In our case, the data will now be sorted based on the OrderDate column, or column A.
So far, we’ve used options available from the Google sheets menu.
It is also possible to sort using the function SORT.
The function definition is given below.
SORT function
Sorts the rows of a given array or range by the values in one or more columns.
Sample Usage
SORT(A2:B26, 1, TRUE)
SORT({1, 2; 3, 4; 5, 6}, 2, FALSE)
SORT(A2:B26, C2:C26, TRUE)
Syntax
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])
- range – The data to be sorted.
- sort_column – The index of the column in range or a range outside of range containing the values by which to sort.
- A range specified as a sort_column must be a single column with the same number of rows as range.
- is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order. FALSE sorts in descending order.
- sort_column2, is_ascending2 …
- [ OPTIONAL ] – Additional columns and sort order flags beyond the first, in order of precedence.
Notes
- range is sorted only by the specified columns, other columns are returned in the order they originally appear.
While the previous methods available from the Data menu will sort the data in place, the SORT function generates output, leaving the source data intact.
It is important therefore, to place the formula with the SORT function call, in a cell reference that will provide enough width and height in blank columns and rows, so as not to overwrite other data.
For this reason, in our example we enter the formula in cell I2.
This will leave the original data as it was in its unsorted state, and output the date-sorted data in cells I2 to O44.
The formula itself is as follows:
=SORT (A2:G44, 1, TRUE)
The first parameter, A2:G44, indicates the range of cells that are to be sorted.
Just as with the case when we sorted using the menu option Data > Sort range, we cannot just specify the OrderDate column, we need to specify all columns in the range.
If we only specified the OrderDate column, then only this column would be sorted, while the remaining data would remain unchanged.
The second parameter in the SORT function specifies on which column to sort.
If we had wanted to sort on the Region column, then the value passed would have been 2.
Since our date column is the first column, the value passed is 1.
Finally, the last parameter in our example formula (TRUE), indicates that the sorting will be in ascending order.
We would have had to set this to FALSE if we had wanted to indicate descending order.
SORT can further accept optional parameters, to specify sorting on a second, third, etc. columns.
That is, primary sorting would be on the column OrderDate, and then where there are more than one row with the same date, we could further sort these by Region or Rep.
Conclusion
Sorting by date in Google Sheets is really no different to sorting by any other type.
The only thing to be wary of is the date format.
Other than that, it is no different than sorting by any other numeric type.
Sorting can be accomplished from either the Data menu, where there is a choice of using a selected range or the entire sheet, or through a formula using the SORT function.
The difference between the two methods is that sorting via the menu options will sort the original data, while the SORT function will leave the original data as is and output the sorted data to a new range.
