To filter dates by month in Excel, select a cell in your date column, choose Data > Filter, open the column’s filter arrow, and select Date Filters > All Dates in the Period, then choose a month. To show a particular month in a particular year, use the year-and-month checklist or filter a date range; the best method depends on whether you want one month-year or that month across multiple years.
Use Excel’s built-in month filter
Excel’s date filter is the quickest option when the column contains actual Excel dates. It can hide rows that do not match the month you choose without changing or rearranging the underlying records.
- Make sure your data has a header row, such as Order date, and that each date is in the same column.
- Click a cell in the data. On the Windows desktop, select Data > Filter. Excel adds a drop-down arrow to each header. If the data is an Excel table, filter arrows may already be present.
- Open the arrow in the date column and choose Date Filters > All Dates in the Period.
- Choose the month you want. If Excel presents a year-and-month tree, expand the relevant year and select the month beneath it.
The filter hides nonmatching rows; it does not delete them. To see all records again, open the same column menu and choose Clear Filter From [column name], or use Data > Clear to clear filters across the data range. On Mac, the Filter command is also on the Data tab, though ribbon layout and menu wording can differ slightly by Excel version. Excel for the web offers column filter menus too, but the exact controls may be arranged differently from desktop Excel.
Choose between a month and a month-year
“Show January” can mean two different things: every January in the dataset, or January in one specific year. That distinction matters when a list spans multiple years. For example, a report with orders from 2023, 2024, and 2025 may need only January 2025, or it may need all January orders for a seasonal comparison.
If the filter menu displays months nested under years, selecting January beneath 2025 gives the specific month-year. To combine January from several years, select January for each applicable year if the menu allows multiple selections. If the hierarchy is awkward or the data covers many years, add a helper column for the month number; filtering that column for 1 returns January rows regardless of year. This also makes the intent clear to someone else using the workbook.
- One month-year: use the date hierarchy or filter between the first and last date of that month.
- Same month across years: use a month-number helper column, or select the month in each year in the date filter.
- Repeated reporting: use a helper column or a saved, clearly labeled report process so the same rule can be applied again.
Filter a specific month and year with date boundaries
A date range is a dependable way to isolate one month. For January 2025, the range begins on January 1 and ends on January 31. In the date column’s filter menu, choose Date Filters > Between, then enter the start and end dates if those options appear in your version. Confirm the dates use a format Excel recognizes, such as 1/1/2025 and 1/31/2025 in a US regional setting.
For manual filtering, check that the range includes the intended first and last day. If the records contain times as well as dates, a filter based on the first day through the last day may behave differently depending on how the end boundary is entered. A reliable formula condition for timestamps is “on or after the first day and before the first day of the next month.” That includes every time on the last day without relying on a time value of exactly midnight. For a normal date-only list, the built-in Between filter is often simpler.
For example, April 2024 starts on April 1 and the next month starts on May 1. A comparison using April 1 as an inclusive lower limit and May 1 as an exclusive upper limit includes all April dates, including times such as 4:30 p.m. on April 30, while excluding May 1. This boundary pattern is useful in formulas, database queries, and reports that include timestamps.
Use a helper column for the same month across years
A helper column gives each date a month number that can be filtered independently of its year. Add a header such as Month number beside the date column. Assuming dates are in A2:A100 and the first date is in A2, enter =MONTH(A2) in the helper column’s first data row, then fill it down. If A2 is January 14, 2025, the formula returns 1. Filter the helper column to show 1 for all January records, 2 for February, and so on.
Month numbers are usually more practical than month names for filtering because they are consistent values. If a visible label is more useful to readers, create a separate month-name helper, but remember that names such as January repeat each year and do not identify a unique reporting period. A month-year key is better when the report should distinguish January 2024 from January 2025.
Assuming the date is in A2, this formula returns the first day of the date’s month: =DATE(YEAR(A2),MONTH(A2),1). For A2 equal to January 14, 2025, the result is January 1, 2025. Format the helper column as a date, then filter for the first day of the desired month. The underlying value remains a date, so Excel can sort and compare it chronologically.
Return matching rows with a formula
If you want a separate live results area rather than hiding rows in the original list, Excel for Microsoft 365 includes the FILTER function. Assuming dates are in A2:A100, the complete records to return are in A2:C100, and the desired month is January 2025, enter =FILTER(A2:C100,(A2:A100>=DATE(2025,1,1))*(A2:A100<DATE(2025,2,1)),"No matching rows") in an empty cell. If the sample data has two January records, the result is those two rows; if none match, it displays “No matching rows.” The comparison includes January 1 and excludes February 1, so it also handles timestamps during January.
FILTER is available in Microsoft 365 and Excel 2021 or later. Its results spill into adjacent cells, so leave enough blank space below and to the right, and do not place the formula inside an Excel table. If your Excel uses semicolons rather than commas as formula separators because of regional settings, use the separator Excel expects. For a recurring report, you can refer to a cell containing the target month’s first day and derive the next month boundary, rather than editing two dates in the formula each time.
Check that the values are real dates
Excel stores genuine dates as numeric serial values and displays them using a date format. Text that looks like a date is not necessarily a date value. A pasted column can contain a mix: some cells may be recognized dates, while others are text because of import settings, extra spaces, or a mismatch between the source’s date convention and the computer’s regional settings.
A quick clue is alignment: dates entered as numbers commonly align to the right by default, while text often aligns left, though formatting can override alignment. You can also change a sample cell’s number format to General. A true date displays as a serial number; text usually stays displayed as text. Do not rely on appearance alone, because custom formats and manually applied alignment can disguise the difference.
If the filter menu offers text filters instead of date filters, or the month filter does not behave as expected, inspect the source values. For a clean column of dates stored consistently as text, select the cells and use Data > Text to Columns, then follow the wizard’s steps and choose the appropriate date order when prompted. Make a copy first if the source has mixed formats, because converting ambiguous entries such as 03/04/2025 depends on whether the original means March 4 or April 3. After conversion, verify several records against the source.
Common problems and practical fixes
- The filter shows only text options: Excel may not recognize the column as dates. Check for text values, leading spaces, blanks, or mixed date formats, and convert the data carefully.
- Some expected rows disappear: A cell may contain a time component, a text date, or a date outside the assumed year. Check the underlying values and use a start-inclusive, next-month-exclusive range when timestamps matter.
- The list has no filter arrows: Click inside the data and select Data > Filter. Check that the headers are in one row and that the data does not have blank rows or columns splitting it into separate ranges.
- Only one year’s month appears: The filter may group months under years. Expand the other years and select their month entries, or use the MONTH helper column to collect that month across years.
- Dates sort strangely: Text dates can sort alphabetically rather than chronologically. Convert them to actual dates, then sort or filter again.
- New rows are not included: If the filtered range was selected manually, records added below it may fall outside the range. Convert the data to a table with Insert > Table so the range can expand as records are added.
Which method should you use?
For a quick one-time check, use the built-in date filter. It requires no extra columns and is easy to clear when the review is finished. For one month in one year, use the date hierarchy or a Between filter, especially when the data spans multiple years. For a recurring comparison of the same month across years, a month-number helper column is usually easier to maintain and explain.
Use a formula such as FILTER when the original table should remain unfiltered or when a separate report should update as its source data changes. Keep the source data intact, give helper columns descriptive headings, and avoid converting dates to text just to make them look like month labels. Date values preserve sorting, date arithmetic, and compatibility with other Excel features.
Frequently Asked Questions
How do I filter January in every year in Excel?
Use a helper column with the date in A2 and enter =MONTH(A2) in the first helper row, then fill down and filter for 1. Alternatively, select January under each year in the date filter if Excel displays a year-and-month hierarchy.
How do I filter a specific month and year?
Choose the month under the relevant year in the date filter, or use Date Filters > Between with the first and last dates of that month. If values include times, a formula or helper approach using the next month’s first day as an exclusive limit avoids missing late-day records.
Can I filter by month without changing the original dates?
Yes. Excel’s built-in filter only hides nonmatching rows. A helper column also leaves the original date values unchanged, and a FILTER formula can display matching rows in a separate area.
Why does Excel not show Date Filters?
The cells may contain text rather than recognized date values, or the selected column may not be the date column. Check the cell values and formats, and convert text dates only after confirming their date order and meaning.
Can I filter dates by month in Excel for the web?
Yes. Select the filter arrow in the date column and use the available date-filter controls. The web interface may arrange options differently from the Windows desktop app; a helper column with MONTH is a straightforward alternative if the month grouping is difficult to use.
Related
- Excel Data Validation List Based on a Cell: How It Works
- Excel Data Validation List: Keep Dropdowns in A-to-Z Order
- Excel Data Validation List Auto Update: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Add a Sort List in Excel: Create a Custom OrderTo add a custom sort list in Excel for Microsoft 365 on Windows, go to File > Options…
- Excel INDIRECT Sheet Name: Build Dynamic Sheet ReferencesTo use a cell’s text as a worksheet name in Excel, combine it with the cell or range…
- SUBSTITUTE and REPLACE in Excel: Differences ExplainedIn Excel, use SUBSTITUTE to find specified text and replace it, or use REPLACE to change characters at…
- Excel MIN IF Multiple Criteria: How to Find the Smallest MatchThe direct answer: Excel has no single MINIFS function, but you can combine MIN with the IF function…
- Excel Formula Codes: A Practical Function ListExcel formula codes usually means formulas and the function names inside them: each formula begins with an equals…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.