How to Group Dates by Filter in Excel (3 Easy Methods)

This article shows you how to group dates by filter in Excel. We commonly need to filter data by date in Excel for a variety of reasons. We can do it in different ways. Here, we will take you through 3 easy and convenient methods on how to group dates by filter in Excel.

How to Group Dates by Filter in Excel: 3 Handy Methods

In Excel, grouping data can help you show a selection of data to study. For example, you might want to divide an enormous list of date and time fields in Excel into months and years.

Here, we have a Sales Report of some Sales Reps containing their consecutive Order Date and Sales.

Now, we’ll filter those data by date to get just our desired date’s sales amount or to get the total sales amount of our preferred timeline.

1. Using Filter Command to Group Dates by Filter in Excel

To begin, we’ll utilize Excel’s AutoFilter feature or simply the Filter command to filter data by date. The Filter command has a variety of options that are very useful in this situation. We have 3 ways of filtering.

1.1. Utilizing AutoFilter Simply

First, we’ll use the AutoFilter feature simply by checking or unchecking the boxes before the Order Date. Follow our steps below.

Steps:

• Initially, select any data or the entire dataset on your worksheet. Then go to Editing > Sort & Filter > Filter.

Note: Also, you can enable the AutoFilter option by pressing CTLR + SHIFT + L.

• The Filter option is now enabled, and the Filter icon can be found on the right side of each Header. Click the Filter icon in the Order Date Header to filter dates.

• Then you’ll see a variety of options presented, such as the image below.

• At this moment if you want to filter data only for the month of January and February, check those boxes and uncheck the rest and click OK.

• Look, how Excel is only displaying data for the months of January and February. You can notice that row 9 to row 14 is hidden because those rows don’t contain data from January and February.

1.2. Applying Date Filters

Let’s have a look at some dynamic filtering options in Excel Filter now.

Steps:

• Open the filter choices by pressing the Filter icon in the Order Date’s header, just as you did in the previous section. Now choose Date Filters > Last Month.

• It will show the filtered data for the last month. As it is June, data for May month will be shown here.

1.3. Implementing Custom AutoFilter Option

We can issue the command manually instead of utilizing the built-in command by selecting Custom Filter from the Date Filters options. Let’s use the Custom AutoFilter to filter the data between the dates of 1/02/2022 and 31/05/2022.

Steps:

• Click Date Filters > Custom Filters.

• Instantly, a Custom Autofilter dialog box opens. The two boxes above are for the start date and the remaining lower two’s are for the end date. Now fill up boxes sequentially like the picture below to get the data between 1 Feb to 31 May of the year 2022 and click OK.

• Then we’ll obtain all of the filtered data for the dates we’ve chosen.

2. Creating Table and Grouping Dates

In method 1, we filtered our data from the normal range of cells. But we can make it more visually appealing by inserting a Table, and then filtering by date from it.

Steps:

• First, select the entire range of cells containing data. Then, select Insert > Table.

• In the Create Table dialog box, our data range is already selected. So, click OK.

• Like our previous method, we can see the Filter Data icon beside each of the headers. You can use this icon to navigate the Date Filters option.

• Click on the Filter icon in the Order Date’s header and you can filter your data by date easily just like our previous method.

3. Create Pivot Table to Group Dates

The most effective method to group dates is to create a Pivot Table. Pivot tables are useful because they enable anyone to filter and retrieve information from the data set they’re working with. Pivot tables enable anyone to see their data from a variety of angles. Now follow the steps.

Steps:

• At first, to create a pivot table select the range of data in our Dataset worksheet. Then, from the Insert tab select Pivot Table.

• PivotTable from table or range dialog box opens. In the table selection box, our dataset is already selected. Choose New Worksheet to place our pivot table and click OK.

• The new worksheet interface will look like the image below. Drag down 2 fields Date and Sales into Rows and Values respectively.

• At this moment, our pivot table is looking like this.

• Again, there is a drop-down arrow beside each header. Click on the arrow beside Row Labels’ header and you can filter data by date just like Method 1.

• To group dates, select any date from Row Labels and right-click on it. Choose the Group option from the list.

• Instantly, the Grouping dialog box opens. You can see that starting and ending dates are automatically set. Now, choose Months and Years from the list to group the data by this condition. Next, click OK.

• You can see that our table is magically transformed to show the data in months and years instead of the date format. We can easily visualize the monthly total sales. Here our dates are grouped into months as per our preference.

• Also, we can group dates on a weekly base. To do this, select Days and write down 7 in the Number of days box in the Grouping dialog box. Then, click OK.

• Our pivot table shows just like below.

Our Order Dates are now grouped into weeks with a range of 7 days.

Possible Reasons If You’re Unable to Group Dates by Filter in Excel

Filters categorize dates by year, month, and day.  This is quite useful for getting a synopsis of the date ranges and applying filters rapidly. What if, however, the filter has abruptly ceased to group dates? Here are some probable causes and solutions!

Reason 01: If Grouping Dates in Filters Is Disabled

The simplest thing to verify is whether grouping dates is enabled in Excel’s settings.

Solution:

• Select File > Options > Advanced > Check box Group dates in the AutoFilter menu.

Back on your worksheet. Is the grouping working now?

Reason 02: When Your Filter Doesn’t Cover All Rows

Make sure your filter encompasses everything in your list or table.

To be sure, remove the Filter and then add it again. To remove the Filter follow the steps below.

Solution:

• First, select filtered cells on your worksheet. Then select as follows.

Editing > Sort & Filter > Filter.

• For instance, your filtered range of cells will look like this.

• Again, you can add a Filter following Method 1

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

<< Go Back to Date Filter | Filter in Excel | Learn Excel