How to sort by date in Excel

Xiaobai Software  2022-08-10 11: 54  read 85 views

ExcelOne of the most useful features in , is to sort your data by date.Maybe you need to schedule your family members' birth dates to send cards, sort out year-end budget deals, or track sales by month.

OnExcel, there are several ways to sort your entries by date.You can sort the data in ascending or descending order, and you can sort by day, week, month, or year.We'll walk you through all the different options on how to sort by date in Excel.


How to sort an Excel column by ascending or descending date

In Excel, sorting a single column into ascending or descending date order is easy:

1. Select the data you want to sort.

2. From the menu bar (also called the Ribbon) at the top of the screen, under the "Home" tab, select "Sort and Filter".

3. Then select Sort Oldest to Newest or Newest to Oldest.


Alternatively, you can achieve the same result by selecting the data and selecting Sort using the right-click.Then choose the order that works best for your data.


This is fine if you're just sorting a simple list of dates and plan to enter the corresponding data later.Sorting like this always puts your dates in chronological order, first by year, then by month, then by day.

If you use Excel spreadsheet templates to organize your life or have tables of data, you may need to use other methods instead of sorting by date in Excel, which we'll cover next.

How to sort entire table by ascending or descending date in Excel

When sorting dates in a table, it is helpful to add headers at the top of each column.For example, if you make a basic Gantt chart in Excel, you will have a list of tasks, corresponding to a list of dates.Below, we'll show you how to use these headings as categories to sort your data.

When you sort data in a table, it's important to highlight all the data.If you highlight only one column, you will sort only that column, leaving the corresponding columns in their original order.Then the date won't match the correct data.

If you spot a mistake right away, you can click Undo to go back to the original arrangement.Otherwise, you may have to revert to the previous document version or re-enter the data.Fortunately, Excel usually catches this kind of error in time to prevent it from happening.

If you try to sort with only dates selected, Excel should give you a warning and ask if you'd like to expand your selection.When the error message pops up, select Expand selection and click Sort.


Then hover over the data selection.Highlight the missing items by dragging the small green square in the lower left corner and try sorting again.


Select the title and data you want to sort.Then click Home > Sort & Filter to choose your sort order.


Note that this will only work if the date is in the first column of the table (in this case, column A).In Excel, if you want to sort by dates when they are listed in different columns, you need to use custom sort.

Use Excel's custom sort to sort by date

Custom Sort is another handy way to sort by date in Excel.If your data has headers, you can use them as categories to easily sort the entries even if they are not in the first column.

In the example below, a simple sort like the one we've shown you will sort the items in the transaction column alphabetically instead of putting your dates in order.Since the dates are in the second column, we need to use a custom sort to tell Excel to sort the dates in column B:

1. Highlight the header and the data under the header.

2. On the Excel Ribbon, click Home > Sort & Filter > Custom Sort.Check the box in the upper right corner to use your data title as the sort category.

3. Next to Sort By, select Date from the drop-down menu.Select Oldest to Newest or Newest to Oldest from the Order drop-down menu.


Custom sorting also lets you choose multiple sorting categories.For example, you can use this feature if you want to see transactions listed in one column before another.

In the Custom Sort box, select Sort By > [First Column].Then click add level > then press > [second column].Click OK to sort your table.


Sort by month using Excel function

In some cases, you may want to extract the month from the entered date.An example is putting the birthdays of all your colleagues in order so you can thank them all with monthly birthday cakes.For your purposes, the month is important, but the year is irrelevant.

There are many basic Excel functions and formulas that can help you make quick calculations or extract data from cells. The MONTH function is one such function.

Using the MONTH function, extract the month number for each entry from the full date.First, add a column titled Month.Click on the first empty cell under the month.In this case it will be C2.Enter =MONTH(B2), where B2 refers to the first entry in the date column.


The MONTH function will output the numbers from 1 to 12 in the corresponding cells.The numbers represent the months in chronological order, so January = 1 and December = 1.To quickly enter a function in each cell, select the cell that contains the formula.In this case it is C12.Then, click and drag the green square that appears at the bottom corner of the cell all the way to the bottom of the column.


Now you can sort dates by month in Excel so you don't miss anyone on your list.Highlight the entire table, including the header > right click > click Sort & Filter > Custom Sort > Sort by Month.


You can also use a function to sort dates by year.Follow the instructions above and replace the MONTH function with =YEAR(B2).The result will be an ordered list of years.


Sort by month and day with Excel formula

The MONTH function is quick and easy if you don't need the dates in order, but let's say you want to celebrate each birthday individually.A formula can help here.

Add a column called "Birthday".In the empty cell (C2) next to the first date, type =TEXT(B2,"MMDD"), where B2 refers to the first date.This returns a list of dates in the format MMDD, excluding the year.For example, 12/07/1964 would become 1207.

Do a custom sort to put your birthday list in order.Since birthdays are formatted as text, not dates, Excel will give you a warning.Pick anything that looks like a number as Number Sort > OK and your list will be in the correct order.


What if sorting by date doesn't work in Excel?

If you applied the methods discussed above and Excel is not sorting by date correctly, please check that you entered in a format that Excel understands.If all numbers are left-aligned in their cells, you might store them as text.To fix the format, click the Text drop-down menu in the Excel ribbon and choose a date format.


An Easy Way to Sort by Date in Excel

Sorting dates in the correct order may feel mundane, but it's an important task that must be done accurately.So let Excel's sort function sort the dates efficiently.The ability to sort by date in Excel is super useful and has multiple applications, as we've seen here.

Address of this article:
Copyright Notice:The article only represents the author's point of view, the copyright belongs to the original author, welcome to share this article, please keep the source for reprinting!