Data organization is vital to extract insights efficiently, and well-organized spreadsheets are the best way to achieve that. Sorting data by different criteria such as name, size, and date can make it more understandable. Spreadsheet software such as Google Sheets and Microsoft Excel offers powerful sorting tools to rearrange your data easily. This article focuses on the various methods to sort a dataset by date in Google Sheets.
Sort by range functionality
The most straightforward and quickest method to sort data in Google Sheets by date is by using the sort range feature. It takes the original dataset and arranges it based on the desired sort date.
Here’s how it works:
- Open the sheet with the data you want to sort on your Windows laptop or Chromebook
- Highlight the relevant data fields that include dates, names, and other data along with their headings (do not include serial numbers)
- Click the “Data” option in the Sheets menu bar
- Hover over “Sort range” and select “Advanced range sorting options”
- In the Sort range dialog box, tick the “Data has header row” checkbox if you highlighted headings (leave unchecked otherwise)
- Choose the date column as the column to sort by
- Select “A→Z” for ascending order or “Z→A” for descending order
- Click the “Sort” button
Your data will be arranged instantly based on the date column. It’s recommended to create a backup of your dataset before sorting to avoid any accidental data loss
Sort by sort function
While the sort range method is useful for sorting data once, the sort function is a better choice when making frequent changes. It creates new fields instead of modifying existing ones, making it more dynamic. Plus, it’s useful for sorting single and multiple columns.
Here’s how to use the sort function:
- Open the sheet with the dataset you want to sort
- Select an empty column where the sorted data will be populated
- Enter the formula “=SORT(A1:B5,1,TRUE)” in the function (fx) bar at the top of the page. “A1:B5” represents the sort range, “1” represents the sort column (the first column), and “TRUE” denotes ascending order
- Press the Enter key on your keyboard
Your data will be sorted automatically, and new cells will be populated starting from the cursor’s position. The sort function preserves the original data column, enabling you to revert to it anytime. Besides, it organizes future data for as long as it’s within the sort field’s range, making it a useful tool.
Check the validity of your date format
A correct date format is essential for the date sort function to work. Google Sheets recognizes valid dates by aligning them to the right by default. However, if you have invalid dates, the algorithm reads them as plain text and yields incorrect results. Here are two ways to check the validity of your date format.
Check the alignment
Valid dates are right-aligned in Google Sheets, even if they contain special characters such as $, /, or :. If your dates are left-aligned, they are likely in an incorrect format. Generally, typing out the date in full or using the MM/DD/YYYY or MM-DD-YYYY format are valid.
Use the DATEVALUE function
If the data spans the cell width, it can be challenging to discern alignment. In that case, use the DATEVALUE function in the adjacent columns to check for errors and get the numeric value of the dates.
Set your cursor in an empty cell, input the “=DATEVALUE(C2:C12)” formula, or whatever range contains your valid dates, and press Enter.
In our example, the function returns the value 44470 for the date 10/1/2021 and a #VALUE! error for the invalid 03.03.2021.
Sort your Google Sheets like a pro
Sorting data is crucial, especially for large volumes from different sources. Sorting data using fields with numbers, dates, times, and positions can make it easier to comprehend and extract meaningful insights. While this article discusses sorting data by date, you can sort using several other parameters. Such as sorting plain text in alphabetical order.
