We can assess the dates in our worksheet with the Sort & Filter tool. In this tutorial, we will illustrate how to arrange dates in Excel and how to sort dates in chronological order using the Sort dialog box.
Figure 1 – Categorizing data
How to sort dates
1. First, we must ensure that our date values are placed in date format. To do this:
a. We will highlight Cell A4: A12 and right-click on our selection
b. Next, we will select Format cells
Figure 2 – How to sort by date
c. In the Number tab, we will select Date and click OK.
Figure 3 – Categorizing data
2. To quickly sort our dates, we will again, highlight the entire table
Figure 4 – Sort by date
3. Next, we will click on the Sort & Filter tab which can be found in the Editing tab.
Figure 5 – Sort by date
4. We can tap “Oldest to newest” or “newest to oldest”
Figure 6 – Sort dates in chronological order
5. If we previously selected only the dates column, we may receive a prompt from Excel like this:
Figure 7 – How to sort by date
6. When we have this prompt, we must select “Expand the Selection” and then select “Sort”
How to sort multiple columns using the sort dialog
When we have multiple columns with dates and times, it is important we use the Sort dialog box so that we can specify the exact cells we want to sort.
- We will highlight the Cell A4 to Cell E12
- Next, we will select Home
- We will select Sort & Filter to see the drop-down list
- We will tap Custom Sort and go to the Sort dialog box
Figure 8 – How to arrange dates
- Under the Column heading, we will select Sort by down arrow and tap “OrderDate” simultaneously to sort that column
- Under the Sort On heading, we will select Cell values
- Under the Sort Order heading, we will select “Oldest to Newest” from the drop-down list
- We will ensure that we check “My data has headers”
Figure 9 – How to sort multiple columns
- Lastly, we will select OK in the dialog box
Figure 10 – How to organize data by date
When carrying out a quicksort in a worksheet that contains texts and numbers mixed together, Excel will quickly sort both into separate lists, with the records with text data coming at the bottom of the sorted list.
In many other events, we may receive a warning from Excel such as:
- Sort anything that looks like a number as a number
- Sort numbers and numbers stored as text separately
If we pick the first option, then Excel will place text data in the correct location of the sort results. If we go with the second option, then Excel will separate text from date data. To avoid this situation, we must always change to the date format first before sorting data.