To filter dates by year in Excel for Microsoft 365 on Windows, open the filter arrow in the date column and select the year from the date checklist or choose Date Filters > All Dates in the Period and then the year. If Excel does not recognize the entries as dates, add a helper column with =YEAR(A2), fill it down, and filter that column to the year you need.
How Excel date filters work
Excel can offer year, month, and day choices when a column contains actual date values. Although a cell may display a date such as 6/14/2024, Excel normally stores that date as a serial number and applies a number format to show it as a date. The filter uses the underlying value, not just the way the cell looks.
When Excel recognizes the column, its filter menu may display dates in a tree grouped by year, month, and day. Selecting a year includes its dates and excludes dates in other years. In some menus, the same choice is available through Date Filters > All Dates in the Period. The precise list depends on the dates present in the filtered range.
A year filter does not change or delete the source data. It hides rows that do not meet the condition. Clear the filter to show them again. This is useful for reviewing one year of orders, expenses, appointments, or other dated records without making a separate copy of the data.
Filter a date column to one year
- Check that the data has a header row and that each record occupies one row. Avoid blank rows or blank columns inside the data range, because they can prevent Excel from detecting the full list.
- Click a cell in the range. On the ribbon, choose Data > Filter. Excel adds a dropdown arrow to each header. If arrows are already present, filtering is on.
- Open the arrow in the date column. If you see a hierarchical date checklist, clear Select All and select the checkbox beside the year you want. Expand the year if you need to inspect its months or days.
- If the menu instead offers date-filter commands, choose Date Filters > All Dates in the Period, then choose the required year. Excel keeps rows whose dates fall in that year.
- Check the visible results and the status bar or row numbers to confirm that the filter is active. The filter arrow typically shows a filter icon, and excluded rows are hidden.
For example, selecting 2024 should show dates from January 1 through December 31, 2024, while hiding 2023 and 2025 records. It does not matter whether the dates display as 6/14/2024, 14-Jun-2024, or another date format, as long as they are real Excel dates.
To remove only this condition, reopen the date-column dropdown and choose Clear Filter From followed by the column name. To remove filters from all columns, use Data > Clear. The filter arrows can remain available after clearing the conditions.
Use a helper column when the year menu is missing
A helper column is a straightforward fallback when the date checklist is not grouped by year, the dates are imported inconsistently, or you want a visible year value to use in more than one filter. Add a header such as Year in a blank column beside the source data.
Assuming the dates are in A2:A100 and the new Year column starts in B2, enter =YEAR(A2) in B2 and fill the formula down through the records. The formula returns the year number for a valid Excel date: if A2 contains June 14, 2024, the result is 2024. Turn on the filters if needed, open the Year column dropdown, and select 2024.
Keep the helper column within the same data range so its values stay aligned with their records. If new rows are added later, fill the formula into those rows too. Converting a well-formed range to an Excel table with Insert > Table can help: tables include filter arrows and generally extend formulas and formatting to new table rows.
The helper value is numeric, so it can also be sorted numerically and used in summaries. It is not a replacement for the original date: keep the date column if you need to filter by month or day, calculate elapsed time, or preserve the exact date of an event.
Filter by an exact date range instead
For a one-time year filter, the year checklist is usually fastest. A date range is useful when the year is not a calendar year, when you need a partial year, or when you want to make the boundaries explicit. In the date column dropdown, choose Date Filters > Custom Filter. Set the first condition to “is after or equal to” January 1 of the starting year and the second to “is before” January 1 of the following year; join the conditions with And.
For calendar year 2024, those boundaries are January 1, 2024, inclusive, and January 1, 2025, exclusive. The exclusive upper boundary matters if cells contain times as well as dates. A value such as December 31, 2024 at 3:00 p.m. is later than midnight at the start of December 31, so a condition that stops at that midnight can omit records from the final day. “Before January 1 of the next year” includes every time on December 31.
Enter dates in a format Excel recognizes for your regional settings. If a typed date is interpreted incorrectly, use the date picker where available or check the cell’s actual value before relying on the filter. For a fiscal year, use its real start and next-year boundary rather than choosing a calendar-year group.
Diagnose dates that will not filter properly
The most common cause of a missing year hierarchy is that some entries are text rather than dates. This often happens after importing a CSV, copying values from a website, or combining data entered under different regional date conventions. Text that looks like 6/14/2024 may not be a date value Excel can group.
To check a particular cell, assuming A2 contains the suspect entry, enter =ISNUMBER(A2) in an empty cell. A genuine Excel date normally returns TRUE, because its stored value is numeric; a text date returns FALSE. This check identifies the cell’s data type, but it does not fix the entry.
- Inspect the source: Look for leading apostrophes, spaces, inconsistent separators, or dates with day and month order that conflicts with your regional settings.
- Convert carefully: If the values are consistent, select the affected cells and use Data > Text to Columns to convert them, choosing the correct date order when prompted. Check a few results before using the converted column.
- Check for mixed values: A column can contain both real dates and text dates. Converting only some rows may leave the filter behaving inconsistently.
- Refresh the filter view: Clear and reapply the filter after correcting values if the dropdown still shows the old choices.
Another source of confusion is a date-time value. Excel stores a time as a fractional part of a date’s serial number. A year filter should include such values in the correct year, but custom range boundaries need to account for time, as described above. Also confirm that the header is not being treated as a data row and that the filter range includes every record.
Choose a filter method for the job
Use the built-in date checklist for quick inspection of a clean date column. It is convenient when you need to switch between years or months and do not need an extra field in the data. Use a helper year column when the date grouping is unavailable, when you want a persistent year field, or when a report needs to filter or summarize by year repeatedly.
Use a custom date range for fiscal years and other nonstandard periods, or when you need precise inclusive and exclusive boundaries. For a reusable report that summarizes amounts by year, a PivotTable may be more suitable than hiding rows: select the data and choose Insert > PivotTable, then place the date field in Rows and the measure to summarize in Values. If Excel recognizes the dates, you can group the PivotTable dates by years. Filtering a list and summarizing it are different tasks; a filter hides records, while a PivotTable reorganizes and aggregates them.
These approaches do not require a special cost or add-in in desktop Excel for Microsoft 365. A normal filter or helper column is enough for most lists. The main requirement is consistent source data and a clearly defined date period.
Windows, Mac, and Excel for the web
In Excel for Microsoft 365 on Windows, the usual ribbon route is Data > Filter, followed by the dropdown in the date header. The Windows shortcut Ctrl+Shift+L turns AutoFilter on or off for the selected range. On Mac, use Data > Filter as well; the interface is similar, though menu placement and available commands can vary by build. The common Mac shortcut for toggling filters is Command+Shift+F.
Excel for the web also supports filtering tables and ranges from their header dropdowns, though the available menu options can differ from desktop Excel. If the web menu does not provide the date grouping you need, use a Year helper column or open the workbook in the desktop app. In every version, verify that the entries are real dates before interpreting a missing year option as a filter limitation.
Frequently Asked Questions
Can I filter dates by year without adding a column?
Yes. If Excel recognizes the date values, open the date column’s filter menu and select the year in its grouped checklist or through Date Filters > All Dates in the Period. A helper column is only needed when the built-in grouping is unavailable or a separate year field is useful.
Why does Excel show individual dates instead of years?
Excel may show a flat checklist if the range is not recognized as a date column, contains mixed text and dates, or has values that do not support date grouping. Check the cell type with =ISNUMBER(A2) and confirm that the range contains consistent date values.
Will filtering by year include dates with times?
Yes, a year filter should include date-time values in the relevant year. When creating a custom range, use a start boundary at the beginning of the year and an exclusive boundary at the start of the next year so times on the final day are not accidentally left out.
How do I filter a fiscal year?
Use Date Filters > Custom Filter with the fiscal year’s first date as the inclusive lower boundary and the first date after the fiscal year as the exclusive upper boundary. For repeated reporting, a helper column that labels each date with its fiscal year may be easier to reuse.
Does filtering by year delete the other years?
No. A filter hides rows that do not match; it does not remove their data. Choose Clear Filter From in the column menu, or use Data > Clear to clear active filters across the range.
Related
- Excel Data Validation List: How to Exclude Blanks in Excel
- Excel Data Validation List From Another Sheet: Steps
- Excel Data Validation List From a Table Column Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Negative Numbers in Excel: Formula and TipsIf your values are in A2:A20, count the negative numbers with =COUNTIF(A2:A20,"<0"). COUNTIF returns the number of cells…
- Excel Autofill Keyboard Shortcuts for Fill Down and RightTo autofill a selected range in Excel, include the source cell and the cells you want to fill,…
- Clear Excel Sort: Remove Sorting or Restore Original OrderTo clear a sort in Excel for Microsoft 365 on Windows, select a cell in the sorted data…
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- Excel Compare Columns in Two Sheets: Methods and FormulasTo compare values in the same row, put this formula in a spare column and fill it down:…
- Excel Max Index: Row, Column, and INDEX Function LimitsIn current Microsoft Excel for Microsoft 365, the largest worksheet row index is 1,048,576, and the largest column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.