Group Dates in Excel Filters: Steps, Fixes, and Tips

How-to basics · Updated November 20, 2026

To group dates in an Excel filter, make sure the column contains real Excel dates, turn on filtering with Data > Filter, and open the date column’s dropdown. Excel usually displays recognized dates in a year-and-month hierarchy; expand or collapse a year, then select the date groups you want to show. For a period such as this month, use the dropdown’s Date Filters options instead.

How date grouping works in an Excel filter

Excel stores valid dates as serial numbers and applies a date format to display them as calendar dates. When a filter recognizes a column as dates, its dropdown can present the available dates in a hierarchy rather than as one long, flat list. Depending on the dates in the column, you can expand a year to see months and, where available, individual days. Selecting a year or month filters the rows; it does not change or reorganize the source data.

This is different from sorting. A filter hides rows that do not meet the selection, while sorting changes the order of rows. It is also different from grouping dates in a PivotTable, which creates grouped categories for summarizing values. These distinctions matter because a filter is usually the quickest way to display a subset of a list, but it does not create a monthly summary by itself.

Steps to group dates in a worksheet filter

  1. Check that your data has a single header row and a date column with a heading such as Order Date. Keep the records together without completely blank rows or columns inside the range.
  2. Click a cell in the data. If it is already an Excel table, its header row should have filter arrows. Otherwise, on Windows, choose Data > Filter, or press Ctrl+Shift+L to add or remove AutoFilter arrows. On a Mac, use Data > Filter if the shortcut differs in your keyboard setup.
  3. Open the arrow in the date column. If Excel recognizes the values as dates, look for a hierarchical list organized by year. Expand the year you need, then select or clear the relevant year, month, or day checkboxes.
  4. Choose OK to apply the selection. Excel hides rows outside the selected dates. The row numbers may skip, which is normal for filtered data.
  5. To return to all records, open the same dropdown and choose Clear Filter From the date column, or clear the filter using Data > Clear.

If the column is part of an Excel table, filtering one column works with the other table filters: Excel shows only rows that meet all active filter selections. The table’s data remains intact, and clearing a filter makes hidden records visible again.

Filter by a month, quarter, or rolling period

For a relative date period, open the date dropdown and look for Date Filters. Options can include periods such as This Month, Last Month, This Quarter, This Year, or Year to Date, as well as Before, After, and Between. The exact choices can depend on the version, the values, and the selected date column. These filters are useful when you want a period based on the current date, rather than manually checking specific calendar dates.

To show a month such as January across multiple years, look under Date Filters for All Dates in the Period and choose the month, if that option is available in your Excel build. This is not the same as selecting January under just one year in the hierarchy: that selection shows only that year’s January. If you need a repeatable monthly report, check that the resulting rows include the years you intended.

For a fixed date range, use the Between option when available, or set a custom filter with a start and end date. For example, a range from April 1 through June 30 includes dates in the second quarter only if the end condition includes June 30. If records include times as well as dates, a value recorded late on June 30 may be stored with a time component; confirm that your range includes the full final day.

What the filter changes—and what it does not

A date filter controls which rows are visible. It does not convert text into dates, rewrite the underlying values, or group the records into new worksheet rows. It also does not automatically create totals by month. If you copy visible cells, take care to copy only visible results when that is the intention; filtering alone has not deleted the other records.

Filtering is useful for questions such as which orders were placed in one month or which invoices are overdue before a selected date. It is less suitable when you need a durable report with one row per month, monthly totals, or a chart organized by calendar period. Those tasks are better handled with a PivotTable or a separate reporting layout.

Check that Excel recognizes the values as dates

If the dropdown shows Text Filters instead of Date Filters, or lists values as unrelated strings, some or all of the entries may be text. A cell can look like a date but still contain text—for instance, a value imported from another system using a different date order. Changing the number format alone does not convert text into a date.

Check a few cells by selecting them and inspecting the formula bar and number format. Also look for inconsistent alignment, apostrophes, spaces, blank cells, or a mixture of formats. A column with both real dates and text dates can produce confusing filter choices because Excel cannot treat every entry consistently.

For a small, carefully checked set of text dates, convert the values using Data > Text to Columns and select the source date order in the final step, such as MDY or DMY. Make a copy of the column first if the original values need to be preserved. The correct order depends on how the source data was written; choosing the wrong order can turn a date such as 04/05/2026 into the wrong calendar day. After conversion, apply a recognizable date number format and check sample values before filtering.

Common problems and practical fixes

  • No date hierarchy appears: Confirm that the cells contain actual dates and that the filter includes the full data range. Text entries, blank rows, or an incorrect range can prevent the expected behavior.
  • Some rows disappear unexpectedly: Check for other active filters in the table. Excel combines filter conditions across columns, so a date selection and a category selection may both be limiting the visible rows.
  • A month selection misses dates: If you chose a month nested under one year, Excel is filtering that year’s month, not necessarily that month in every year. Use All Dates in the Period if offered, or select the required months under each year.
  • The date list is hard to navigate: Use Date Filters for a range or relative period rather than checking many individual days. If the dropdown contains many distinct text-like entries, investigate whether the column has mixed data types.
  • New records are not included: If the filter was applied to a fixed range rather than an Excel table, new rows added below the range may not be covered. Convert the range to a table with Insert > Table, or adjust the filtered range.

Before changing imported dates, preserve a backup or duplicate of the original column. This is especially helpful when date conventions are ambiguous or the values came from a report that may need to be reproduced.

When to use a PivotTable instead

Choose a PivotTable when the goal is to summarize dates, not merely hide rows. Select the source data and choose Insert > PivotTable. Place the date field in Rows, then place a numeric field in Values to create a summary. In many Excel versions, you can right-click a date in the PivotTable and choose Group, then select options such as Months and Years. Including Years is important when the source spans multiple years: grouping by month alone can combine, for example, January 2025 and January 2026 into one January category.

PivotTable grouping requires usable date values. Blank or non-date entries in the date field can prevent grouping or cause errors, so clean the source column first. PivotTables also summarize data separately from the original rows; they do not replace worksheet AutoFilter. Use a regular filter to inspect individual records and a PivotTable to compare totals by month, quarter, or year.

Frequently Asked Questions

Can Excel filter dates by month without grouping them manually?
Often, yes. Open the date column dropdown, choose Date Filters, and look for All Dates in the Period, then select a month. Availability can vary. Selecting a month under a particular year in the hierarchy limits the result to that year.

Why does Excel show Text Filters instead of Date Filters?
Excel may be treating the entries as text, or the column may contain a mixture of text and real dates. Convert the values to consistent, valid Excel dates, check the source date order, and reopen the filter dropdown.

How do I filter dates between two days?
Open the date dropdown and choose Date Filters > Between if that choice appears. Enter the start and end dates and confirm the range, especially if the cells include times as well as dates.

Does grouping dates in a filter change my data?
No. Filtering hides rows that do not match the selected dates; it does not delete them or change their date values. Clear the filter to display the rows again.

How can I group dates into months and show totals?
Use a PivotTable rather than relying on the filter list. Put the date field in Rows, add the measure to Values, and use the PivotTable Group command to choose Months and, when needed, Years.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.