If Excel’s filter dropdown is not grouping dates by month, first check that every value in the date column is a real Excel date, not text, and that the filter covers the whole list. Select the date column’s dropdown and try Date Filters > All Dates in Period; if Excel still treats the entries as ordinary text or separate values, repair the dates or add a month helper column.
How Excel date filters work
Excel stores valid dates as numbers and displays them using a date format. For example, a cell may display 6/15/2025 while its underlying value is a number representing that date. When a filtered list contains date values, Excel can offer date-specific commands and a hierarchical checklist organized by year, month, and day. The exact checklist appearance can vary with the data and Excel version.
A date format alone does not make text into a date. If a cell contains the characters 6/15/2025 rather than an Excel date value, formatting it as a date may leave it unchanged as text. That is a common reason the filter shows individual entries alphabetically or as a long flat checklist instead of grouping them by month.
Also distinguish an ordinary worksheet filter from a PivotTable. A worksheet filter narrows the rows already in a list; a PivotTable can group a date field into months or years as a report feature. The two tools have different controls, so a grouping instruction for a PivotTable will not necessarily apply to a normal filter dropdown.
Check whether the entries are real dates
Start with a few cells that look representative, including the first and last dates and any imported or manually entered entries. Select a cell and inspect the formula bar. A date stored as a number will generally be right-aligned by default, while text is often left-aligned, but alignment is only a clue because formatting can override it.
To test a cell directly, enter a check in an unused column. With the suspected date in A2, the formula =ISNUMBER(A2) returns TRUE for a numeric date value and FALSE for text. Excel dates are numeric serial values; a time, if present, is stored as a fractional part of the number. If some rows return TRUE and others FALSE, the column contains mixed types and should be standardized.
Look for other common signs of inconsistent data:
- Some dates use a different order, such as day/month/year in one row and month/day/year in another.
- Imported values contain leading or trailing spaces, apostrophes, or an unrecognized text format.
- Cells contain labels such as “Unknown,” dashes, or notes mixed in with dates.
- Some cells are blank, or the column has a heading or subtotal inside the data.
Be careful with ambiguous dates such as 04/05/2025. Depending on regional settings, that can mean April 5 or May 4. Confirm the intended order before converting the column; otherwise a successful conversion can still produce the wrong dates.
Convert text dates into Excel dates
For a consistent column of text dates, select the cells and use Data > Text to Columns. In the wizard, choose Delimited and click Next, leave the delimiters unselected, click Next, then choose Date and select the order that matches the source data, such as MDY or DMY. Click Finish and verify several converted cells, especially ambiguous ones. The exact wizard presentation may vary slightly between Excel releases.
This conversion is most appropriate when the text follows a consistent date pattern. If the source data is inconsistent, first separate or correct the different patterns rather than applying one date order to the entire column. A formula can also convert text when Excel can interpret it: with the text date in A2, =DATEVALUE(A2) returns a numeric date serial if the value is recognizable under the computer’s regional date settings. For example, if A2 contains recognizable text for June 15, 2025, the result is the corresponding Excel date, which can be formatted to display as 6/15/2025. Copy the results and use Paste Special > Values if you need to replace the original text column.
If the dates came from a CSV or another system, consider importing them again and specifying the correct data type or regional locale during the import. That avoids relying on Excel to guess what ambiguous text means. After converting, apply a date number format if necessary; formatting controls appearance, while conversion changes the underlying value.
Make sure the filter covers the full data range
A filter cannot group rows it does not include. Click a cell in the list and inspect which rows have filter arrows and whether the full date column is part of the filtered range. If rows were added below the original range, they may be outside an ordinary filter, particularly if there is a blank row separating them from the list.
To refresh a worksheet filter, select a cell in the data and use Data > Filter to turn filtering off and back on, then inspect the range. You can also use Data > Clear to remove current filter criteria before applying a new one. Clearing criteria does not convert text dates or repair the source values; it simply removes the active filtering conditions.
For a list that grows regularly, format the data as an Excel table with Insert > Table or Ctrl+T on Windows (Command+T on Mac). Confirm that the selected range includes the headers and all rows. Table filter buttons are attached to the table columns, and the table normally expands as adjacent rows are added. Check for blank rows or columns that could cause Excel to detect only part of the intended dataset.
Use the month filter or a date range
When Excel recognizes the column as dates, open its filter dropdown and look for Date Filters. Choose All Dates in Period and then the required month, if that option is available in the dropdown. This is useful when the goal is simply to show, for example, every record from June, regardless of the year-selection details in a checklist.
For a specific month and year, a date range is often clearer and avoids selecting the wrong year. Open the dropdown, choose Date Filters > Between, and enter the first day of the month and the first day of the following month as the boundaries. For June 2025, that means using June 1, 2025 through June 30, 2025 if the dialog treats both endpoints as included. If the records contain times, an inclusive end date can exclude entries later in the day only if the comparison is set incorrectly; check the dialog’s operators and resulting rows. Another dependable approach is a PivotTable or a helper column that identifies the month and year.
Do not choose a month name alone when records from multiple years must be distinguished. “January” can refer to January 2024, January 2025, and other years. A month-and-year field makes the intended period explicit.
Build a month helper column
A helper column is practical when the filter checklist remains inconvenient, when users need a stable month label, or when the source data has an unusual layout. Assume valid dates are in A2:A100 and put a month key in B2. Enter =DATE(YEAR(A2),MONTH(A2),1) and fill it down. For a date of June 15, 2025 in A2, the formula returns June 1, 2025; format column B with a display such as mmmm yyyy to show June 2025. Because the underlying helper values are actual dates, they sort chronologically.
If a text label is more useful, with the same source date in A2 use =TEXT(A2,"yyyy-mm"). For June 15, 2025, it returns the text 2025-06. This label sorts in calendar order when it uses the year-first pattern, but it is text rather than a date. Keep the source date column if you need day-level filtering, calculations, or a true chronological date value. In either case, make sure the source cell is a valid date before filling the formula down.
When the data is in a PivotTable
PivotTable date grouping is separate from the regular filter checklist. In a PivotTable, right-click a date item, choose Group, select Months, and include Years as well when the source spans multiple years. Grouping by months without years can combine January from different years into one category, which may not be what a report needs.
If Group is unavailable or Excel reports that the selection cannot be grouped, inspect the source date field for blanks, text, errors, or mixed values. Correct the source, refresh the PivotTable, and try again. A PivotTable based on a data model may have different grouping behavior; in that case, use a calendar table or add an appropriate date field to the model rather than assuming worksheet filter commands apply.
Common problems and practical checks
If the filter still looks wrong after conversion, check these issues before rebuilding the workbook:
- Hidden or blank rows: A blank row can split the list and cause Excel to filter only one section. Remove the separating row or create a table from the complete range.
- Mixed date and text values: Retest several cells with ISNUMBER. Converting only the visible sample is not enough; check the whole column or use a temporary test formula and filter for FALSE.
- Wrong regional interpretation: Verify day and month order against the original source. A displayed date can look plausible but still represent the wrong day.
- Stale criteria: Clear the existing filter and reapply it after correcting the values. Existing selections may not reflect newly added entries.
- Time portions: Two cells can display the same calendar day while holding different times. Use a date-only helper or appropriate date boundaries if time-level values matter.
- 1900 versus 1904 date systems: Workbooks can use different date systems, especially when values move between files or platforms. If dates shift by a fixed number of days after copying, inspect Excel’s workbook date-system setting and verify the source before changing it.
For diagnosis, make a copy of the sheet, add a test column, and compare a handful of known records with the original source. Avoid mass replacement until the date order and the affected range are clear. Filtering hides rows; it does not delete them, so clearing the filter should reveal the full list again.
Excel for Mac and Excel for the web
The underlying distinction between numeric dates and text applies in Excel for Windows, Mac, and the web. Ribbon placement and dialog details can differ by release. On Mac, the table shortcut is Command+T rather than Ctrl+T, and menu or ribbon labels may be arranged differently. Excel for the web also supports filtering, but some conversion and import dialogs available in the desktop app may not have identical controls; open the workbook in desktop Excel if you need the full Text to Columns wizard.
Keyboard shortcuts can also vary with the keyboard and system settings. Prefer the visible Data tab controls if a shortcut behaves differently. Regardless of platform, verify that a converted cell is a number and that the filter includes all rows before concluding that month grouping is broken.
Frequently Asked Questions
Why does Excel show dates as separate items instead of months?
Excel may be treating the entries as text, the column may contain mixed values, or the filter may cover an incomplete range. Confirm the values are numeric dates and try Date Filters > All Dates in Period.
How do I filter a date column by month and year?
Use a date range covering the first through last day of the required month, or add a helper column that returns the first day of each month and format it to display the month and year. Including the year prevents different Januaries from being confused.
Does changing the cell format convert text to a date?
No. A number format changes how a recognized number is displayed; it does not reliably convert text characters into a date serial. Use Text to Columns or a conversion formula, then verify the result.
Why can’t I group dates in my PivotTable?
Check the source field for blanks, text, errors, or inconsistent entries, then correct the source and refresh the PivotTable. Also confirm that the PivotTable’s data source and model support the grouping operation you are trying to use.
Can I filter the same month across several years?
Yes. Use a month-only condition or group by month without year when combining years is intentional. If each year must remain separate, filter a precise date range or use a month-and-year helper field.
Related
- Excel European Number Format: Decimals and Separators
- Excel Subscript Out of Range: Causes and Fixes
- Excel Engineering Number Format Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Minus 1 Year: Formula and Leap-Year RulesTo subtract one calendar year from a date in Excel, use =EDATE(A2,-12), assuming the original date is in…
- Excel Conditional Formatting for Dates: Rules and FormulasTo conditionally format dates in Excel, select the date cells and choose Home > Conditional Formatting > Highlight…
- Excel Formula to Calculate Days Between Dates, ExplainedTo calculate elapsed days between two dates in Excel, subtract the earlier date from the later one. With…
- Data Validation List Formula: Excel Examples and SetupIn Excel, a data validation list formula goes in Data > Data Validation > Settings > Allow: List…
- DAX SUMIF: How to Sum Values by ConditionDAX has no standalone SUMIF function. To sum a column when a condition is true, create a measure…
- Average Between Two Dates in Excel: Formulas & TipsTo average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.