To filter a date column to one calendar year in Excel, select your data, choose Data > Filter, open the date column’s dropdown, then choose Date Filters > All Dates in the Period > Year and select the year. Excel hides rows outside that year; it does not delete them. Before you begin, make sure the column contains real Excel dates, not dates stored as text.
What You’ll Need
- An Excel workbook with a header row and a column containing the dates you want to filter.
- A complete dataset, including any related columns you want to keep together when rows are hidden.
- Microsoft Excel for Microsoft 365 on Windows for the menu paths below. Excel for Mac and Excel for the web offer filters too, although labels and menu placement can vary slightly.
You do not need a formula or a special date format to use the built-in filter. A date may display as 11/3/2024, Nov 3, 2024, or another regional format and still be a valid Excel date. What matters is that Excel recognizes the cell’s underlying value as a date.
Step-by-Step
- Check the layout of your data. Put a clear heading at the top of the date column, such as Order Date or Invoice Date. Keep one record per row, and avoid blank rows or merged cells inside the data. Include all related fields—such as customer, order number, and amount—within the same data range. This helps Excel hide or show whole records rather than separating a date from its corresponding details.
- Select a cell in the data. Click any cell in the range or Excel table. If the data is a continuous block with a header row, Excel usually detects its boundaries automatically. If it is not, select the complete range yourself, including the headings and every column that belongs to each record. Do not select only the date column, or the filter could leave related information visible when its row should be hidden.
- Turn on filtering. On the Windows ribbon, choose Data > Filter. Excel adds a dropdown arrow to each column heading. If your range is formatted as an Excel table, filter arrows are usually already present; click the arrow in the date heading and continue. You can also create a table from a range using Insert > Table, making sure the option indicating that the table has headers is selected.
- Open the date-column menu. Click the dropdown arrow in the date heading, not an arrow in another field. If the menu shows a checklist of years and dates, clear Select All, then select the year you want. Depending on your Excel build and the contents of the column, the dates may appear in a hierarchy: click the expand control beside a year if needed, and select or clear the appropriate items.
- Use the year filter when it is available. In the dropdown, point to or open Date Filters, choose All Dates in the Period, then select the year. Excel applies a calendar-year filter, so a choice such as 2024 includes dates from January 1 through December 31, 2024. The menu’s wording or arrangement may differ slightly between builds; if you do not see this choice, use the year checklist or the helper-column method below.
- Confirm the filtered results. Look at the visible dates and the status bar or row numbers. Every visible date should belong to the selected year, and row numbers may skip because the other rows are hidden. Check a few records at the beginning and end of the results. If you are filtering sales or other related records, confirm the details in the same rows still match the visible dates.
- Change or remove the filter when needed. Open the date heading’s dropdown to choose a different year, or select Clear Filter From followed by the date-column heading. To remove filters from the whole range, use Data > Filter to turn off the filter controls. Clearing a filter makes the hidden rows visible again; it does not restore a deleted value because filtering does not delete values.
- If the built-in year choice is missing, add a helper column. Insert a column beside the dates and give it a heading such as Year. In the first data row, enter a year-extraction formula that refers to that row’s date cell, then fill it down. For example, if the date is in A2, the formula is
=YEAR(A2). Filter the helper column to the year you need. This approach is also useful when you want an explicit year field to use in a pivot table or report.
For example, if a table has dates from 2022 through 2025 and you choose 2023, Excel shows the records whose dates fall within 2023. It will not show a date from December 2022 or January 2024, even though those dates are close to the selected period. If you need a fiscal year that starts in a month other than January, the built-in calendar-year option may not match your reporting period; use a helper column or a custom date range instead.
On Excel for Mac, the Data tab also includes filtering controls, and the date dropdown provides filtering options. Menu placement and wording can vary by version, so look for the dropdown on the date header and its date-filter choices. In Excel for the web, use the filter arrow on the header and choose the available date or year selections. If your web version does not offer the exact period option you need, open the workbook in the desktop app or use the helper-column approach.
Common Mistakes
- Filtering only part of the table. Selecting just the date column can disconnect the visible dates from other fields. Apply the filter to the full data range, or click inside a properly formed Excel table so Excel treats each record as one row.
- Using text that looks like a date. A value such as 2024-03-15 may look date-like but still be text. Excel may then show text-filter options instead of date filters, or sort values in an unexpected order. Check whether the values behave like dates and whether the date filter menu appears.
- Expecting a calendar-year filter to mean a fiscal year. The Year option refers to January through December. For a year that runs from, for example, July to June, filter between the actual start and end dates or create a helper column that assigns each row to the correct fiscal year.
- Leaving an old filter in place. If a different column is also filtered, selecting a year may still show fewer records than expected. Check the filter icons in all headers and clear other filters before deciding that dates are missing.
- Including a blank row or missing header. Excel can treat a blank row as the end of the range, leaving later records outside the filter. Remove unintended blank rows, add a header for every column, and reapply the filter to the complete dataset.
Tips
- Convert a stable range to a table. Select the data and choose Insert > Table. Tables include filter arrows and typically extend to include new rows added directly below them. This can make recurring year-based checks easier, particularly when the dataset grows over time.
- Keep the original date column. A helper column containing years is convenient for summaries, but retain the underlying dates. The original values let you filter by a specific start and end date, group by month, or check individual transactions later.
- Use a precise date range for custom periods. If you need a period that does not match a calendar year, open the date dropdown and look for Date Filters options such as Between. Enter the intended start and end dates, and verify the boundary dates in the results. This is often clearer than trying to make a calendar-year selection represent a fiscal year.
- Be careful with date and time values. A cell can contain both a date and a time, even if the display shows only the date. A calendar-year filter should group the record by its date, but use a date-range check when results near midnight or period boundaries are important to your report.
- Keep a note of the active criteria. Filtered lists can look like complete datasets because hidden rows are simply out of sight. If you share a workbook, state which year is selected, or clear the filters before handing it off when the recipient should see all records.
Troubleshooting
The dropdown has Text Filters instead of Date Filters. Excel may be reading the entries as text. Check a few cells by changing their number format to a date format; if the display does not respond as a date would, the underlying values may need conversion. One practical option is to select the affected column and use Data > Text to Columns, follow the wizard, choose the date order that matches the source values when prompted, and finish. Work on a copy first if the column contains mixed formats, because a wrong date order can change the meaning of dates such as 03/04/2024.
The filter hides every row or shows none of the expected records. Clear the date filter, inspect the source values, and check whether the year is truly present. Confirm that the date column does not contain inconsistent text, errors, or dates entered with an unexpected day-and-month order. If you are uncertain, sort the column from oldest to newest and inspect representative cells, then apply the filter again.
Some records appear outside the selected year. Check whether those cells contain text rather than dates, or whether a date-and-time value is being displayed without its time. Also check for an additional filter in another column. Clear all filters, reapply only the year filter, and compare the full displayed date values around the year boundary.
New rows do not respond to the filter. The original filter range may not include them. If the data is a normal range, turn off the filter, select the expanded range, and choose Data > Filter again. If it is a table, add new records directly beneath the table or resize the table so the rows are included.
You cannot find the year option. Check the dropdown on the actual date field, and make sure Excel recognizes its entries as dates. If the menu still lacks the period option, select the year in the date checklist if available, filter a helper column with =YEAR(A2) where A2 is the first date cell in the row, or use a custom date range. Feature labels can differ across Excel versions and platforms.
Frequently Asked Questions
Can I filter more than one year at a time?
Yes. In the date dropdown’s checklist, select each year you want to include. If the menu offers only a single period choice, use the checklist or a helper Year column and select multiple year values there.
Does filtering by year delete the other dates?
No. Filtering hides rows that do not match the criteria. Clear the filter to display them again. Avoid deleting rows unless you deliberately want to remove the records from the workbook.
How do I filter for a year in a fiscal calendar?
Use a custom start-and-end date filter if the fiscal period is a single continuous span. For repeated reports, add a helper column that assigns each date to the appropriate fiscal year, then filter that column.
Can I filter by year if my dates are in different display formats?
Yes, if the cells contain valid Excel dates. Different display formats do not prevent date filtering. If Excel treats the values as text, convert them to real dates first and confirm the order of day, month, and year is correct.
Will the filter include dates with times?
A valid date-and-time value is still based on a date, and the year selection is intended to show records from that calendar year. If an exact time boundary matters, use a custom date range and inspect the full values rather than relying only on the displayed date.
Can I filter by year without changing the original data?
Yes. The built-in filter changes which rows are visible, not the stored dates. A helper column adds a calculated field but leaves the original date values in place; you can remove the helper column later if it is no longer needed.
Related
- How to Edit Custom Number Formats in Excel Step by Step
- How to Edit Border Color in Excel: Step-by-Step Guide
- How to Duplicate a Text Box in Excel Without Rebuilding It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Check If an Excel File Is Corrupted: Signs and FixesTo check whether an Excel file is corrupted, make a copy and try opening it in Excel; if…
- Excel Formula: Days Between Two Dates and Today’s DateTo calculate days between two dates in Excel, subtract the earlier date from the later one: with a…
- Identify Offsetting Entries in Excel With Formulas and ChecksTo flag entries with an equal and opposite amount, put the date in column A, account in B,…
- Excel Accounting Formulas Cheat Sheet for Daily BooksAn Excel accounting formulas cheat sheet starts with =SUM(range) for totals, a simple addition formula for running balances,…
- CONCAT in Excel: Join Text and NumbersYes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a…
- Can’t Filter in Excel? Causes, Fixes, and WorkaroundsYes—Excel can filter a list or table, but the Filter command may be unavailable, the dropdown arrows may…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.