When dealing with a large dataset in your spreadsheet, your data might go haywire. For these instances, the sorting function can come in handy to keep your data organized and keep you on track.
Thankfully, in Google Sheets, the sort feature is built into the tool. So, if you are looking for a way to sort certain dates, like your family’s birthdays, or review your monthly transactions, then this article is here to help you out.
So, without further ado, let’s explore the different methods you can apply to sort your data by date in Google Sheets.
Before We Begin
Please ensure that you have entered a date format that Google recognizes. The sorting function might fail to work in some cases due to the incorrect formatting entered on your dataset. So, to check whether the dates in your cells are valid, you can use the Data Validation feature on Google Sheets. Here is a step-by-step guide you can follow.
- First, open up your Google Sheet.
- Select the Data Range you want to check the validation for.
- Head over to the menu bar and click on the Data option.
- Now, select Data Validation from the list of options.
- Select Date and is Valid Date from the drop-down menus in the Criteria section.
- In the On invalid data section, check the option for Show Warning.
- Finally, click on the Save button to save changes on your spreadsheet.
Now, head back to your dataset. Check to see if there are any red linings in your cells that say if it is an invalid date. If you notice any invalid dates in your range, we suggest changing them right away.
Additionally, we recommend adjusting the text alignment to be the same throughout your dataset range, as this might also affect when sorting out your dates in Google Sheets.
How to Sort By Date In Google Sheets on PC
After checking the data validation of your dataset and making the necessary formatting changes, let us now move on to how we can sort by dates in Google Sheets. Depending on your personal preference, you can use the Sort Function or the Sort Formula on your data range. Let’s look at both of these methods in more detail down below.
Using Sort Function
One of the easiest ways to set and arrange by date is through the sort function. Here, you will only need to highlight the data range and apply the sort by date function. Here are some steps you can follow.
- First, open up the Google Sheets you want to edit.
- Select the date range you want to sort.
- Navigate to the menu bar and click on the Data option.
- Here, from the list of options, select Sort Range.
- From the drop-down menu, you can either choose to sort through A to Z or Z to A (ascending or descending order.)
- Also, if the selected data has a header row, please ensure you check the box for it.
- Alternatively, you can also click on the Advanced Range Sortings option and sort the dates.
- To apply the changes, click on the Sort button.
You can also access the Sort function by right-clicking or clicking on the drop-down arrow on the row header and selecting your preferred sorting option.
Using Sort Formula
Another alternative method you can apply to sort out your dataset by date is to use the Sort Formula. But, before using this formula, we suggest you make a separate column heading and label it as the Sorted Dates to avoid confusion.
Here is the formula to be used depending upon the sorting options.
- For arranging data in ascending order: =SORT(Range, column number, True)
- For arranging data in descending order: SORT(Range, column number, False)
If we take an example of number data, your formula should appear like: =SORT(A1:A3,1, True) or =SORT(A1:A3,1, False).
The main advantage of this method is that if you apply any changes to a certain entry, the sorted list would also automatically change. So, you don’t have to bother manually changing each dataset which saves your time and effort.
In some instances, if you want to make changes to the formula cells, you won’t be able to edit it because of the formula and will instead receive the “Value error.” To edit, you will have to convert the data into values. Copy the data range, use the Paste Special option, and select the Values Only option.
How to Sort By Date In Google Sheets on Mobile
For mobile users, you can still use the Sort function through the app version of Google Sheets. However, the steps for sorting might vary to the PC version due to its different interface. So, here are some steps you can follow.
- Open up the Google Sheets app from your device.
- Navigate to the sheet you want to edit.
- Tap on the header column to select all the datasets.
- Now, tap on the header column again to open up the menu option.
- Tap on the right arrow and continue tapping until you see the Sort options.
- For android devices, after tapping the header row twice, click on the three vertical dots to open up the menu settings.
- Now, scroll down and select SORT A-Z or SORT Z-A options to arrange your dataset in either ascending or descending options.
How to Sort Date by Month in Google Sheets?
For sorting out dates through the month option, you will have to use a formula specific to sorting the monthly values. But, before you use the formula, we suggest you create a new column and label it to avoid confusion.
- To assort in ascending order = SORT(B2:B, MONTH(B2:B) ,TRUE)
- To assort in descending order =SORT(B2:B, MONTH(B2:B) ,FALSE)
How to Sort by Date in Microsoft Excel?
The excel application also has a built-in sorting function, making it easier to sort data by date. Here is how you can do it.
- Open up the Excel application from your device.
- Head over to the spreadsheet and select the date range you would like to sort.
- Navigate to the Home tab and click on the Sort and Filter option.
- You can either select the sort oldest to newest or newest to oldest option.
How to Change Your Date Format in Google Sheets?
If you want to make all your dates shown in the cells more cohesive or if you want to switch up your date format to match your preference, then here are some steps you can follow.
- Open up Google Sheets and navigate to the spreadsheet you want to edit.
- Head over to the menu bar and click on the Format option.
- Now, select Numbers from the list of options.
- From the drop-down menu, click on the Date option to change it to the default date layout.
- Alternatively, you can also click on the custom date and time formats option from the same drop-down menu.
- Here, you can select from different types of date layouts or create a custom one.
- After making the formatting changes, click on the Apply button to confirm your action.