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.


Download Practice Workbook

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


3 Methods to Group Dates by Filter in Excel

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.

How to Group Dates in Excel Filter Dataset

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.

How to Group Dates in Excel Filter Using AutoFilter

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.

How to Group Dates in Excel Filter Using AutoFilter

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

How to Group Dates in Excel Filter Using AutoFilter

  • 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.

How to Group Dates in Excel Filter Using AutoFilter

  • 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.

How to Group Dates in Excel Filter Using AutoFilter


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.

How to Group Dates in Excel Filter Using Date Filters

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

How to Group Dates in Excel Filter Using Date Filters


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.

How to Group Dates in Excel Filter Using Custom AutoFilters

  • 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.

How to Group Dates in Excel Filter Using Custom AutoFilters

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

How to Group Dates in Excel Filter Using Custom AutoFilters

Read More: How to Group Dates in Excel Slicer (4 Ways)


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, then filtering by date from it.

Steps:

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

How to Group Dates in Excel Filter Inserting Table

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

How to Group Dates in Excel Filter Inserting Table

  • 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.

How to Group Dates in Excel Filter Inserting Table

  • 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.

How to Group Dates in Excel Filter Inserting Table

Read More: How to Use Excel Pivot Table to Group Dates by Month and Year


3. Create Pivot Table to Group Dates

The most effective method to group dates is to create 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.

How to Group Dates in Excel Filter Inserting 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.

How to Group Dates in Excel Filter Inserting Pivot Table

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

How to Group Dates in Excel Filter Inserting Pivot Table

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

How to Group Dates in Excel Filter Inserting Pivot Table

  • 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.

Inserting Pivot Table

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

Inserting Pivot Table

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

Inserting Pivot Table

  • 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.

Inserting Pivot Table

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

Inserting Pivot Table

  • Our pivot table shows just like below.

Inserting Pivot Table

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

Read More: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions


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.

Enabling Group Dates in Filters

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.

Removing Filter

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

Removing Filter

  • Again, you can add a Filter following Method 1.

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. Please visit our website Exceldemy to explore more.


Related Articles

 

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo