When you try to see a set of data in a spreadsheet from time to time, it often helps to sort data using dates in that range, which allows Microsoft Excel to do so by using the default sorting tool. This is how you do it.
Sorting Date in ascending or descending order
An easy way to sort data in Microsoft Excel by date is to sort them in chronological order (or reverse chronologically). This sorts the data by the earliest or earliest date, depending on your preference.
To do this, open your Excel workbook and select your data. You can do this using a mouse or trackpad, or by clicking on a cell in the range and pressing Ctrl + A on your keyboard.
With your data selected, click the “Sort & Filter” button on the “Home” tab on the ribbon bar. This is where you go to sort values in Excel in various ways, including by date.
In the “Sort & Filter” drop-down menu, all have the option to sort the data in ascending or descending orders. If Excel detects the date in the data you selected, the options must be displayed as “Sort Oldest to Newest” or “Sort Newest to Oldest.”
If you want to sort the data so that the earliest date comes first, click the “Sort Oldest to Newest” option. Otherwise, click “Sort Newest to Oldest” to sort the data so that the most recent date comes first.
After you select the options that you want, Excel will automatically sort the data in that order, using the date column as a reference point.
The first date (under your column label) will be the earliest or the last date in the set.
If you have problems, select only the column that contains the date and then click Home> Sort & Filter> Sort Oldest to Newest or Sort Newest to Oldest.
Microsoft Excel will display a “Sort Warning” box, asking if you want to sort the data around it (the rest of your data) to ensure that the entire data remains intact, if not only the date column that will be sorted.
Make sure that “Expand Selection” is highlighted and then click the “Sort” button.
This will ensure that your entire data set is sorted in ascending or descending order, using dates as reference points.
Sorting Date based on Month or Year
Sometimes it is useful to organize data based on a particular month or year. You might, for example, look to see how many birthdays fall in these groups.
You can do this using the MONTH or YEAR function. This identifies the month or year number from the date and provides it in a separate column. This column can then be sorted in ascending or descending order.
To do this, create a new column called “Month” or “Date” to the right of your existing data. In the first cell below the column label, type = MONTH (A2) or = YEAR (A2), where “A2” is the first cell in your data range that contains dates.
To ensure that the MONTH or DATE formula is used for your entire data set, double-click the small green square icon in the lower-right corner of the cell. This will automatically duplicate the formula for all other rows where it can find the data to the left of it.
With your MONTH or DATE formula, select the “Month” or “Date” column then click Home> Sort & Filter.
From the drop-down menu, select the “Sort Smallest to Largest” or “Sort Largest to Smallest” option. If you sort by the smallest to largest, Excel will sort your data by the first year or month that comes first.
Likewise, Microsoft Excel will sort by the latest year or month if you choose the largest to smallest option.
You must confirm that you want to use the entire data set, so make sure that “Expand Selection” is highlighted and then click “Sort” in the “Sort Window” warning box.
Depending on the options that you choose, Excel will immediately sort your data using the month or year column that you created.