Home life · Updated
How to Organize Rows in Excel by Date
Quick answer Select your data, go to the Data tab, and click Sort. Choose the date column, set Sort On to Cell Values, and pick Oldest to Newest or Newest to Oldest. Excel will rearrange all rows based on the dates.
What You’ll Need
- A computer with Microsoft Excel (2013 or later, including Microsoft 365).
- Your spreadsheet open with the data you want to sort.
- Dates stored as actual dates (not text) for best results.
Step-by-Step
- Select your data range. Click and drag to highlight all the rows you want to sort, including the header row. If your data is in a table, click anywhere inside it.
- Open the Sort dialog. Go to the Data tab on the ribbon. In the Sort & Filter group, click the Sort button (not the A-Z or Z-A icons). This opens the Sort dialog box.
- Choose the date column. In the Sort by dropdown, select the column that contains your dates. If your dates are in a header row, make sure “My data has headers” is checked at the top right.
- Set the sort order. Leave Sort On as “Cell Values”. In the Order dropdown, choose “Oldest to Newest” for ascending order (earliest dates first) or “Newest to Oldest” for descending order (latest dates first).
- Apply the sort. Click OK. Excel will rearrange all rows based on the date column. If you have multiple date columns, repeat the process with the next column using “Then by” in the Sort dialog.
Common Mistakes
- Sorting only one column: If you select just the date column and sort, Excel will reorder only that column, breaking your data. Always select the entire row range.
- Dates stored as text: If your dates are left-aligned in the cell, they might be text. Sorting text dates alphabetically gives a wrong order (e.g., 01/02/2023 before 01/01/2024). Convert them to real dates first.
- Blank cells in the date column: Rows with blank dates are often placed at the top or bottom unpredictably. Fill or exclude them before sorting.
- Merged cells: Merged cells in the date column can cause sorting errors. Unmerge them before sorting.
Tips
- Use a Table (Ctrl+T) for your data. Tables make sorting easier and keep formatting consistent.
- If you need to sort by date and then by another column (like priority), use the “Add Level” button in the Sort dialog.
- For quick sorts, you can also right-click a date cell, choose Sort, and pick “Sort Newest to Oldest” – Excel will automatically select the contiguous data range.
- To sort by day of the week or month (not chronological), use a helper column with formulas like =TEXT(A2,”dddd”) and then sort by that column.
- If your dates include times, make sure the time is part of the date value (e.g., 01/05/2024 14:30) so sorting works correctly.
Troubleshooting
- Sort order is wrong (e.g., 10/10/2024 before 09/09/2024): This usually means your dates are text. Select the date column, go to Data > Text to Columns, and just click Finish (with Delimited selected). This often converts text dates to real dates. Alternatively, use the DATEVALUE function in a helper column.
- Rows are not moving together: You probably selected only one column. Undo (Ctrl+Z) and reselect the full range, then sort again.
- Header row is getting sorted with data: Uncheck “My data has headers” in the Sort dialog if your header is not being recognized, or make sure it’s checked if the header is being sorted.
- Sort button is grayed out: This happens if your worksheet is protected or if you have a filter active. Check the Review tab for sheet protection, and clear any filters from the Data tab.
- Dates are showing as numbers (e.g., 45234): That’s Excel’s serial number format. Select the cells, press Ctrl+1, go to Number tab, and choose a Date format.
Frequently Asked Questions
How do I sort by date in Excel without mixing up rows?
Always select the entire data range (all columns and rows) before sorting. Excel will keep rows intact because it sorts by the entire row, not just the selected column.
Can I sort by date in Excel on a Mac?
Yes, the steps are the same. On a Mac, the Sort dialog is under the Data tab as well. The interface may look slightly different, but the options are identical.
Why is Excel sorting my dates alphabetically?
This happens when dates are stored as text. Check alignment: text is left-aligned, real dates are right-aligned. Convert text to dates using Text to Columns or the DATEVALUE function.
How do I sort by month and day, ignoring the year?
Create a helper column with the formula =TEXT(A2,”MMDD”) and sort by that column. Or use =MONTH(A2)&DAY(A2) and sort numerically.
How do I sort by date in Excel with blank cells?
Blank cells will be placed at the bottom (for ascending) or top (for descending) by default. If you want them elsewhere, fill them with a placeholder date or filter them out before sorting.
Can I sort by date in Excel Online (web version)?
Yes, Excel Online has the same Sort feature. Select your data, go to Data > Sort, and choose your date column and order.
Related
- How to Alphabetize in Excel: Sort A-Z in Minutes
- How to Alphabetize an Excel Sheet: Sort A to Z
- How to Stretch Upper Back Shoulder Blades
Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.