Excel Group Dates by Month in a Filter: Steps & Fixes

Formulas & functions · Updated November 19, 2026

To group dates by month in an Excel filter, make sure the column contains real Excel dates, then open its filter arrow and use the date hierarchy or Date Filters options. If you need a clear month-and-year choice, add a helper column with a month key and filter that column instead.

How Excel handles dates in a filter

Excel stores a date as a number, even when the cell is displayed as “February 19, 2024.” That underlying value lets Excel recognize dates as a sequence and offer date-specific filtering. In a regular filtered range or table, the drop-down for a recognized date column can show a hierarchy of years, months, and individual dates. You can expand or collapse parts of the hierarchy and select the dates or periods to keep visible.

This is different from grouping rows in a PivotTable. An ordinary filter hides rows that do not meet the criteria; it does not combine the remaining rows or add monthly totals. A PivotTable can group dates and summarize records by month. Choose the method based on whether you want to hide records, summarize them, or make a reusable month field for filtering.

Cell formatting alone does not turn text into a date. A cell containing the text “2024-02-19” may look like a date but still be treated as text, depending on how it was entered or imported. That distinction is the most common reason the expected date hierarchy is missing.

Show month groups in a regular filter

For a normal list, first make sure the data has one header row and a date in each relevant record. Select a cell in the data, then turn on filtering with Data > Filter or the Windows shortcut Ctrl+Shift+L. On a Mac, use Command+Shift+F to toggle AutoFilter. Excel tables already have filter arrows in their headers.

  1. Click the arrow in the date column’s header.
  2. Look for a year-and-month tree in the list of date values. Expand a year if its months are collapsed.
  3. Clear (Select All) if necessary, then check the month or date entries you want to keep.
  4. Click OK. Excel hides rows outside the selection; it does not delete them.

If the menu shows a flat list, look for Date Filters. Options such as All Dates in the Period and date comparisons can filter a period without selecting every individual day. The precise options shown depend on the data and Excel build. In Microsoft 365 for Windows, the setting File > Options > Advanced > Display options for this workbook > Group dates in the AutoFilter menu controls whether dates are grouped in the filter menu. If the option is off, turn it on and reopen the menu.

Filter one month across multiple years

“February” can mean February of one selected year, or every February regardless of year. In the date hierarchy, selecting February beneath a particular year keeps only that year’s February records. For a cross-year view, select the February entries under each relevant year if the hierarchy allows it, or use a month-only helper column. This avoids confusing February 2023 with February 2024.

Date Filter commands can also express periods, but they are not all interchangeable. A command such as This Month refers to a calendar period relative to the current date, not necessarily every January or every February in the dataset. For a specific month in a specific year, use a date range or select that month under its year. For a recurring month-of-year analysis across several years, use a PivotTable or helper column.

For example, if your records cover January 2023 through December 2025, filtering the hierarchy to February under 2024 leaves only February 2024. A month-only helper can instead select every February across all three years. Decide which meaning you want before filtering, especially when comparing seasonal activity.

Use a helper column for a reliable month key

A helper column is useful when the date hierarchy is unavailable, when you want month-and-year labels in a standard filter, or when the same filtered view needs to be easy for colleagues to understand. Add a new header such as Month or Month start beside the date column. With valid dates in A2:A100, enter this in B2 and fill it down:

With the original dates in A2:A100 and the helper formula in B2, use =DATE(YEAR(A2),MONTH(A2),1). If A2 contains February 19, 2024, the result is February 1, 2024; format column B as mmmm yyyy to display “February 2024.”

This formula creates a real date for the first day of each record’s month. Therefore, all dates in February 2024 share one key, while February 2025 remains a separate key. To apply the display format, select the helper cells, press Ctrl+1 on Windows (or Command+1 on Mac), choose Number > Custom, and enter mmmm yyyy as the format code. The display changes, but the helper values remain dates that sort chronologically.

If the goal is every occurrence of a month regardless of year, use a month-of-year key instead. With the same date layout, enter =DATE(2000,MONTH(A2),1) in B2 and fill down. For a date in any February, this returns January? No: it returns February 1, 2000. Format the helper cells as mmmm so the filter displays month names. Because the helper dates all use the same year, they sort in calendar order and combine February records from different years.

Group dates in a PivotTable instead

Use a PivotTable when the purpose is to see totals, counts, or other summaries by month rather than merely hide rows in a list. Select the source data and choose Insert > PivotTable. Place the date field in the PivotTable’s Rows area, and place a numeric field in Values if you want a sum or count.

To group a date field, right-click one of its date entries in the PivotTable and choose Group. In the Grouping dialog, select Months; include Years as well when the source spans more than one year and you want each year’s months kept distinct. Click OK. Excel adds grouped fields that can be used in the PivotTable layout and its filters.

Grouping by Months alone can combine January from different years into a single January total. That may be useful for seasonality, but it is not a month-by-month timeline. Selecting both Years and Months preserves the year context. If Group is unavailable, check for text values, blanks, or errors in the date field; a PivotTable generally needs consistent valid dates to group them.

Fix missing month groups

When the filter menu does not show years and months, check the source values before changing the filter. Formatting a text value to look like a date is not enough. One way to convert a consistently formatted imported column is Data > Text to Columns: proceed through the wizard, choose the appropriate date order such as MDY or DMY on the final step, and finish. Confirm the order matches the source; interpreting day and month in the wrong order can silently change dates. For mixed or ambiguous text, inspect and convert a small sample first.

  • Check a suspicious cell: change its number format temporarily to General. A genuine date displays as a number; text remains text. This is a clue, not a complete validation of every row.
  • Look for mixed data: one column may contain real dates, text dates, blanks, or errors. Clean the whole field, not just the first few rows.
  • Check the selected data range: filter arrows should cover the complete list, with headers in the first row and no unrelated blank rows splitting the data.
  • Refresh after edits: if source values changed while a filter was open, close and reopen the menu. For a PivotTable, use PivotTable Analyze > Refresh.
  • Check the grouping preference: in Windows Excel, verify Group dates in the AutoFilter menu under Advanced options if you expect a hierarchy in an ordinary filter.

Also consider whether the cells contain times as well as dates. Excel stores a time as a fraction of a day added to the date. A date hierarchy can still be useful, but exact-value comparisons may distinguish timestamps that appear to share the same displayed date. If filtering by an exact date range, account for the time component or use a helper that returns the month start.

Choose between a date filter, helper, and PivotTable

Each method answers a slightly different question, so there is no single best way for every workbook.

  • Use the built-in date hierarchy for a quick one-off filter on a clean date column. It leaves the source structure unchanged.
  • Use Date Filters for conditions such as dates before, after, between, or within a relative period. This suits date ranges better than a month label.
  • Use a helper column when you need a repeatable month-year key, a month-of-year view across years, or a simple field coworkers can filter directly.
  • Use a PivotTable when you need grouped monthly summaries, counts, or totals rather than a filtered copy of the original records.

For a filtered table that must retain every transaction row, a helper field is often the clearest long-term option. For monthly totals, a PivotTable avoids building a separate summary manually. Filters only hide nonmatching rows; hidden rows remain in the workbook and can be shown again by clearing the filter.

Frequently Asked Questions

Can I filter all January dates without selecting each year separately?
Yes. Add a month-of-year helper using the date formula described above, format it as mmmm, and filter that helper to January. A PivotTable grouped by Months can also combine January across years, though it summarizes rather than simply filtering the source list.

Why does Excel show individual dates instead of months?
The filter may not be grouping dates, or the column may contain text rather than real date values. Check the AutoFilter grouping option in Windows Excel, then verify and convert the source values. Reopen the filter menu after correcting the data.

Does changing the date format group the filter by month?
No. Formatting changes how a value appears; it does not change the underlying value or convert text to a date. Use a real date field, enable date grouping where applicable, or create a helper field.

Can I group dates by month without a PivotTable?
Yes. Use the date hierarchy in a regular filter, a Date Filters condition, or a helper column that returns the first day of each month. PivotTables are needed only when you want their grouped summary layout and calculations.

Will filtering by month delete the other records?
No. AutoFilter hides rows that do not match. Clear the filter from the column’s menu or use Data > Clear to show the full list again. The underlying records remain in the worksheet.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.