To filter month-wise in Excel, select a cell in your data, choose Data > Filter, open the date column’s filter arrow, and select a month under All Dates in the Period. This works when Excel recognizes the column as real dates. If you need a reusable month or month-and-year field, add a helper column and filter that instead. The steps below are for Excel for Microsoft 365 on Windows.
What You’ll Need
- A worksheet with one record per row and a column containing dates.
- A header row with clear labels, such as Date, Customer, and Amount.
- For the helper-column method, an unused column next to your data. The date column should contain valid Excel dates, not text that merely looks like a date.
Filtering hides rows that do not match your selection; it does not delete them or change the underlying date values. If you are working with a shared or important workbook, it is still sensible to save a copy before reorganizing the data.
Step-by-Step
- Check that the data is arranged as a list. Each column should have one heading, and each row below it should represent one record. Remove completely blank rows or columns within the list, because they can cause Excel to apply the filter to only part of your data. Make sure the date heading is in the same header row as the other field names.
- Confirm that Excel recognizes the dates. Select a date cell and look at the formula bar: a valid date is stored as a number, even though it is displayed in a date format. A quick visual clue is alignment: dates often align to the right by default, while imported text often aligns left, although formatting can change alignment. You can also temporarily apply a number format; a recognized date will display as a serial number. Restore a date format afterward.
- Turn on the filters. Click a cell inside the data range, then choose Data > Filter. Excel adds a drop-down arrow to each header. If you have formatted the range as a table, filter arrows may already be visible; you do not need to create a second set. If Excel selects an incomplete range, undo the filter, remove any blank breaks, and select the full list before trying again.
- Open the arrow for the date column. Click the filter arrow beside the date heading, not the arrow on a different field such as customer or category. In the menu, look for Date Filters or All Dates in the Period. The exact menu wording can vary slightly with the data and Excel build. If you see a list of individual dates instead, check that Excel has recognized the column as dates rather than text.
- Choose a month across all years, if that is what you need. Point to All Dates in the Period, then select the month, such as January. Excel keeps rows dated in that month and hides the rest. This kind of month grouping can include January from multiple years. That is useful for comparing recurring seasonal activity, but it is not the same as choosing January of one particular year.
- Choose a particular month and year when needed. If the menu displays a year-and-month hierarchy, expand the relevant year and select the required month. If that choice is not available, use Date Filters > Between and enter the first and last dates of the month. For example, to show January 2025, enter 1/1/2025 as the start and 1/31/2025 as the end. Check your regional date format when entering dates; in some locations, Excel expects day/month/year rather than month/day/year.
- Use a helper column for a more flexible filter. Add a heading such as Month to an empty column. With dates in A2:A100 and the first data row in row 2, enter
=TEXT(A2,"mmmm")in the helper cell on row 2, then fill it down for the remaining records. For a date of 1/15/2025, the result is January. Filter the helper column to show the month you want. This method can show that month across all years, so it is not suitable by itself when you need to distinguish one January from another. - Build a month-and-year helper field for precise grouping. If dates are in A2:A100, enter
=DATE(YEAR(A2),MONTH(A2),1)in the first helper cell and fill down. For a date in January 2025, the result is the actual date 1/1/2025. Select the helper column and choose Home > Number Format, then choose More Number Formats and apply a custom format such asmmm yyyy. The cells will display Jan 2025 while retaining real date values. Filter that column to select the exact month and year. If the list is sorted, this date-based helper also sorts in calendar order. - Review the visible rows and clear the filter when finished. Check the date values and, if relevant, a few related fields to confirm the intended records remain. To remove just one filter, open that column’s arrow and choose Clear Filter From followed by the column name. To clear filters from the whole range, use Data > Clear. This reveals hidden rows; it does not remove your helper column or delete records.
On Excel for Mac, the filter feature is also available from the Data tab, though the arrangement or wording of menu items may differ by release. Excel for the web also supports column filters; if a date-period menu does not offer the grouping you need, use a helper column or a start-and-end date filter.
Common Mistakes
Filtering text that looks like dates. Imported values such as 2025-01-15 may be stored as text. The date-period filter cannot group them reliably as dates. Convert the values to real dates before filtering, and verify that the conversion interpreted day and month in the intended order.
Mixing dates, text, and blanks in one column. A column with some true dates and some text entries may show an unexpected mixture of filter choices. Standardize the whole column, resolve invalid entries, and decide whether blank dates should remain in the data.
Assuming a month filter selects one year only. Choosing January from a month grouping can include January from every year. Use a year-specific choice, a date range, or the month-and-year helper when the year matters.
Leaving out rows or headers. If the selected range does not include all records, some rows will not respond to the filter. A blank row between sections can also make Excel detect a smaller list than you intended. Keep one continuous data range with a single header row.
Sorting month names alphabetically. A text helper that returns January, February, and so on may sort alphabetically rather than in calendar order. Use a real date-based month-and-year helper for chronological sorting, or sort by the original date column.
Tips
Use the built-in date filter when you need a quick view of a month and your dates are already valid. Use a month-name helper when you frequently need to select the same month across different years. Use the date-based month-and-year helper when monthly reports, summaries, or chronological sorting need to keep years separate.
For a reliable filter, keep the original date column even when you add a helper. It allows you to check a filtered record’s exact day and makes it easier to change the reporting period later. Give helper columns clear headings, such as Month or Month Start, so another person can understand why they are in the worksheet.
If you use an Excel table, adding a formula in a new table column generally fills it down for the table’s records. Check the first and last few results, especially after pasting new data. When new rows are added, confirm the table includes them and that the helper formula has been applied.
Before sending a filtered workbook, consider whether the recipient might mistake hidden rows for deleted data. You can clear filters before sharing, or state which filter is active. Filtering is a view of the full list, not a separate extract; copy visible results to another sheet only if you need a standalone subset.
Troubleshooting
The Date Filters option is missing. Confirm that the cells contain real Excel dates. If values came from a text file or another system, convert them using a method appropriate to their format and regional settings. You can try Data > Text to Columns and set the correct date order during conversion, but inspect the result before relying on it. If the values are inconsistent, correct or convert them in a separate column first.
The month filter returns no rows. Check that the year and month are correct, and confirm that the source dates fall within the requested period. Look for hidden time values: a cell may display only a date but also contain a time. A date-range filter through the final day usually includes such records, but a boundary set to midnight at the start of the next day can exclude them. Also check that another column does not have an active filter that is hiding the expected rows.
The filter arrow or some rows are missing. Clear filters, inspect the range for blank rows or merged cells, and ensure every record is inside one continuous list. Click inside the list and turn on Data > Filter again, or resize the table if you are using one. If only part of a worksheet is filtered, select the intended range first and reapply the filter.
The helper column shows numbers or strange results. For the month-name formula, confirm that the formula references the date cell on the same row and that the cell is formatted as General or Text rather than an unexpected number format. For the month-and-year helper, remember its result is a date serial number; apply a display format such as mmm yyyy. If the result is an error, inspect the source cell for text or an invalid date.
New entries do not appear after filtering. Check whether the new records are inside the filtered range or Excel table. Extend the table or range, fill the helper formula into new rows, and reapply or refresh the filter selection. A filter cannot display a row that is outside the range it controls.
Frequently Asked Questions
Can I filter by month without adding a column?
Yes. If Excel recognizes your values as dates, open the date column’s filter menu and choose a month under All Dates in the Period. Add a helper only when you want a reusable field or more control over month-and-year grouping.
How do I show every January, regardless of year?
Use the built-in month grouping if it is available, or create a month-name helper with =TEXT(A2,"mmmm") and filter it to January. Both approaches can include January dates from more than one year.
How do I show only one month in one year?
Select that year and month in the date hierarchy, use a date range from the first to last day of the month, or filter a month-and-year helper. Check the date format Excel expects before typing range boundaries.
Can I filter by month when my dates are stored as text?
Not reliably with Excel’s date-period filters. Convert the text to real dates first, taking care to interpret the original date order correctly. A text-based workaround may be possible for consistently formatted values, but it is less robust than converting the source data.
Will filtering delete the other months?
No. Excel hides nonmatching rows while the filter is active. Choose Data > Clear or clear the filter from the date column to show the full list again.
Why does sorting my month helper put April before February?
A helper containing month names is text, so a normal sort may be alphabetical. Sort by the original date column or use a real date-based month-and-year helper, formatted to display the month and year.
Related
- How to Enable Paste Special in Excel
- Link Quantities in Excel: A Step-by-Step Guide
- How to Enable Freeze Panes in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Times Formula: Multiply Values, Then Add ThemIf quantities are in B2:B5 and unit prices are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5) to multiply each quantity by…
- How to Use the Search Function in Excel: Find Data FastTo find something in an Excel worksheet, press Ctrl+F on Windows (Command+F on Mac), type the text or…
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells.…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- Checklist Examples in Excel: Templates and FormulasUseful checklist examples in Excel include task lists, inspections, inventory counts, event plans, and habit trackers. For a…
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.