How to sort by dates in excel

Dates are added to any business report, be it an accounting document or a table of another nature. To quickly study information, you need to sort the rows and columns. With sorting tools, users can see the information they need in a large database without having to scroll through it and go through every row. Saving time is a good reason to learn Microsoft Excel date sorting and filtering. Let’s find out how to work with sorting tools and filtering types.

Sorting with one condition (ascending/descending)

This method of sorting by date is the simplest. It only requires toolbar options. Let’s make a table where dates play an important role. You must specify the full designation – day, month and year. Sorting is most likely to work correctly if the information is in this format.

1. Select one of the filled cells and go to the “Home” tab. The “Sort and Filter” button will appear at the top right – click it to open the menu.

2. In the list that appears, there are two items sorted by date. Rows can be placed in order from oldest to newest or newest to oldest. Let’s imagine that we need the order from the earliest dates to the latest. Select “Old to New”.

3. Now the dates in the table are in order from 2017 to 2019.

4. Let’s try to put the records in reverse order. Select any cell from the range again and run “Sort Newest to Oldest” from the sort options. The rows are reversed – now the sales volumes for 2019 are higher.

Sorting with multiple conditions (by year/month/day)

In some tables, it is necessary to sort data by date, but not entirely, but by individual days. For example, years in the result should be in ascending order, and some months and days in descending order. Using Excel functions, you can sort the data in this order without spending too much time manually moving rows around. Let’s use the custom sorting function.

Pay attention! To arrange information in accordance with complex sorting conditions, it is necessary to move years, months and days into separate columns. You have to do this because the custom sorter considers the column headings as criteria. An example of dividing a date into components before complex sorting:

1. You need to select any cell in the table and open the sort menu on the Data tab. Click on the item “Custom sorting” – a dialog box will open.

2. We check that in the column “My data contains headers” there is a checkmark. This option excludes the table header from sorting and helps prevent errors. Next, fill in the first level of sorting: according to the conditions, you need to select the column “Year” and the order “Ascending”.

3. Click the “Add Level” button to set sorting by additional conditions. As a result, the dialog box should contain three levels for years, months and days. Select the desired values ​​from the list and click OK.

4. The lines are in the correct order. Days and months go in descending order within the year, because sorting by years was primary. This is indicated by the “Then” labels on the custom sort levels related to months and days.

Sorting by columns in tables with dates is not possible. The program disables this option in the “Options” section of the Custom Sort dialog box.

Automatic or custom filter by date

Microsoft Excel can not only sort data by the dates assigned to them, but also filter rows by year, day, and month. Let’s find out how to use automatic filters by date correctly.

1. Format a range of cells using the “Format as Table” option. This button is located on the “Home” tab, you need to click it after selecting the table. Select any style from the menu and click “OK” in the dialog box – the cells will be painted in the specified colors, and buttons for setting filters will appear in the header.

2. Click the button with the arrow in the column with dates. The program has already considered the data format, so there are filters by date in the menu.

3. Choose any filter to check. For example, the table contains data for the year 2020, so we select the “Last year” option. Click on this list item.

4. There is one line left on the page that matches the conditions. The amount of data can be anything, as long as it matches the selected filter.
5. Let’s use a more complex filter. At the end of the list there is an item “Custom filter” – click on it to set the conditions. You must select the type of filtering and specify the range of values.

6. All lines, except for those within the range of filter values, disappear from the screen.

How to remove filters from a table

Undoing actions is not the best option, it can lead to the loss of important changes. To disable filters and sorting tools, you should use the same tools. Let’s take a look at the previous filtered table and try to remove the filter from it. Click the funnel button in the column heading. The list contains the item “Remove filter from “. Click on it, and the filtering will be canceled – the disappeared cells will return to the sheet.

If the table is formatted, you can remove the buttons for setting filters from it:

1. Select any cell in the range and open the Sort & Filter menu on the Home tab.
2. Click on the “Filter” item – it is highlighted in orange.

The arrow buttons will disappear, but the color theme will remain. You can replace it through the “Format as table” section. The filter menu will not reappear after applying the new settings.

Conclusion

Sorting of Microsoft Excel tables by years, months and days is carried out in a simple and complicated format. The second option is good because the lines can be put in the right order, and the tool will focus on several conditions. It is also worth remembering about filters for cells in the “Date” format. Filtering allows you to see data in the time range that the user needs.