To show dates earlier than today in Excel, select your data and choose Data > Filter, then open the date column’s dropdown and select Date Filters > Before; enter today’s date. That applies a fixed cutoff. For a rule based on the current day, add a helper column using TODAY(), filter it to TRUE, and reapply the filter when the formula recalculates.
What “before today” means in Excel
Excel stores real dates as serial numbers, so it can compare them chronologically. A date filter for values before today keeps dates earlier than the current calendar date and excludes today itself. For example, if today is November 3, 2026, a cutoff of November 3 keeps November 2 and earlier, but not November 3.
Times can affect what a comparison means. A date-time value such as November 2, 2026 at 3:00 p.m. is still before November 3. A value for November 3 at 8:00 a.m. is not before the start of November 3, so it is excluded by a less-than-today test. This is usually the expected behavior when the request is to find records from previous calendar days.
Also distinguish a filter from deleting or changing data. AutoFilter hides rows that do not meet the chosen condition; it does not remove them. Clear the filter to see the full list again.
Apply a one-time date filter
For a cutoff that you choose manually, use Excel’s built-in date filter. In Microsoft 365 for Windows, make sure each column has a heading, click a cell inside the data range, and select Data > Filter. Excel adds dropdown arrows to the headings.
- Open the dropdown arrow on the date column.
- Point to or select Date Filters, then choose Before. In some builds the command is shown as Before… or opens a date-filter dialog.
- Enter today’s date as the cutoff and confirm. Use a date Excel recognizes, such as 11/3/2026, rather than a text label such as “today.”
- Check the visible rows and the status bar or row numbers to confirm that dates on the cutoff date are excluded.
This filter stores the date you entered. It will still mean “before November 3, 2026” tomorrow; it does not reinterpret the cutoff as the new current date. For a date cutoff that should move forward every day, use the helper-column method below.
Excel groups date values in the filter list by year, month, and day when it recognizes them as dates. For a simple list, selecting particular dates can work, but it is less convenient than a date condition when the range is large or contains many distinct days. Date Filters lets you specify a comparison rather than manually ticking many dates.
Make the condition depend on today
A helper column makes the rule visible and easy to audit. Suppose the date values are in A2:A100 and row 1 contains headings. Label B1 “Before today.” With the record date in A2, enter this formula in B2:
=AND(ISNUMBER(A2),A2<TODAY())
Fill the formula down through the rows, then filter column B to show TRUE. ISNUMBER excludes blanks and text entries; the comparison marks a numeric Excel date as TRUE only when it is earlier than today. If A2 contains October 31, 2026 and the current date is November 3, 2026, the result is TRUE. If A2 is November 3, the result is FALSE.
For an Excel table, add the helper heading in a new table column and enter the formula in its first data row. Excel normally fills a calculated-column formula down the table and extends it to new rows. Ordinary ranges need the formula copied or filled down as the data grows. A table is often preferable for ongoing lists because its filter controls and calculated columns expand with added records.
TODAY() returns the current date according to the computer’s date settings, without a time component. It updates when Excel recalculates the workbook, not as a live clock that changes each second. Check Formulas > Calculation Options > Automatic if the results appear stale. After the helper values recalculate, the filter display may need refreshing: open the helper-column dropdown and reselect TRUE, or clear and reapply that filter. A formula changing from TRUE to FALSE does not always cause the currently displayed filtered rows to refresh immediately.
Keep the data range and headings consistent
AutoFilter works best on one continuous rectangular block with a single header row. Avoid blank rows or blank columns inside the list, since they can cause Excel to treat only part of the data as the filter range. Include every column belonging to each record; otherwise, sorting or filtering can separate a date from its associated name, amount, or status.
Before filtering, confirm that the date heading is in the same row as the other headings and that the column contains actual dates. If the data is imported, the cells may look like dates but be stored as text. Text values do not reliably participate in date comparisons, and the filter dropdown may show text sorting options rather than date-specific options. A quick clue is that a date may be left-aligned by default while numeric dates are usually right-aligned, though alignment can be manually changed and is not conclusive.
For frequently updated data, convert the range to a table with Insert > Table, ensuring that My table has headers is selected when appropriate. This helps Excel include new records and preserves the filter controls. Tables do not solve text-date problems, however; imported values still need conversion to real dates.
Understand inclusive cutoffs and time values
“Before today” normally means strictly earlier than today, so the comparison uses less than, not less than or equal to. If the request is instead “today or any earlier date,” include the cutoff date. The distinction matters for due dates and logs: a record dated today is not overdue under a strict before-today test, but it is included under an on-or-before-today test.
For a helper column that should include dates up to and including today, with dates in A2:A100 and the first record in A2, use this formula in B2:
=AND(ISNUMBER(A2),A2<TODAY()+1)
That expression includes every time value on today’s date because TODAY()+1 is midnight at the start of tomorrow and all values from today are smaller. It also includes older dates. For example, with today set to November 3, a value of November 3 at 8:00 a.m. returns TRUE, while a value at or after midnight November 4 returns FALSE. Use this only when the intended rule is through today, not strictly before today.
Fix dates stored as text or unexpected results
If the filter seems to ignore some dates, first check whether those cells are genuine numeric dates. Changing a cell’s number format to Date does not convert text into a date; formatting changes appearance, not the underlying value. A text entry may be converted with Excel’s date tools, but check the source’s day/month order before converting because 04/05/2026 can mean April 5 or May 4 depending on the source convention.
- Look for mixed values: some rows may be real dates and others text, especially after copying from a website or importing a CSV.
- Check for leading or trailing spaces and apostrophes in imported entries.
- Confirm that the helper formula references the correct date cell on each row; a fixed reference such as $A$2 copied down would compare every record against the same cell.
- Check whether the workbook is set to manual calculation if TODAY() results have not updated.
- Reapply the filter after the helper column’s TRUE/FALSE results change.
When a date column includes blanks, ISNUMBER in the helper formula prevents blanks from being treated as earlier dates. It also returns FALSE for text that looks like a date, which is useful for identifying records that need cleanup rather than silently treating them as valid dates.
Excel for Mac and Excel for the web
The basic approach is the same in Microsoft 365 for Mac: select the range and use Data > Filter, then open the date column’s filter menu and choose the date comparison. Labels and dialog details can vary slightly by release. The helper formula using AND, ISNUMBER, and TODAY is supported in current Excel for Mac as well.
On Windows, Ctrl+Shift+L toggles filter arrows for a selected range. On Mac, the corresponding shortcut is generally Command+Shift+F; keyboard shortcuts can vary with Excel version and macOS settings. In Excel for the web, use Data > Filter and the column dropdown; some controls may look different from the desktop version. If a menu item is not present in a particular build, the helper-column method is a practical fallback.
Frequently Asked Questions
Can Excel AutoFilter automatically show dates before today?
Yes, with a helper formula that uses TODAY() and a filter on its TRUE results. A date entered in Date Filters > Before is a fixed cutoff, not a rolling “today” rule. Reapply or refresh the helper-column filter after recalculation if the visible rows have not changed.
Does “Before today” include today’s date?
No. A strict before-today condition excludes today. Use an on-or-before condition if today should be included. If records contain times, a comparison with TODAY() still excludes all times on today’s date.
Why does the Date Filters option not appear?
The column may contain text rather than true Excel dates, or the selection may not be inside the intended data range. Check the values and headings, convert text dates carefully, and confirm that the range has filter arrows. A mixed column can also behave inconsistently.
Will TODAY() update the filtered rows by itself?
TODAY() recalculates when Excel recalculates the workbook, but a filtered view may not immediately refresh when helper results change. Ensure calculation is automatic, then reselect the TRUE filter or clear and reapply it.
Can I filter dates before today without adding a helper column?
Yes, for a one-time cutoff, use Date Filters > Before and enter the current date. For a rule that should roll forward, a helper column is clearer and more dependable than a manually entered cutoff.
Choosing the right approach
Use the built-in Before filter when you need a quick snapshot or a fixed reporting cutoff. Choose the TODAY() helper when the condition must follow the calendar, and remember that recalculation and filter refresh are separate parts of keeping the displayed rows current. If other people will use the workbook, give the helper column a clear heading and leave it visible or document what TRUE means. That makes the result easier to check and reduces confusion when the date changes.
Related
- Excel Nested Group Rows: How to Create and Use Them
- Excel Named Ranges: How They Work and When to Use Them
- Excel Named Range Table: How They Work and When to Use
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Advanced Excel Shortcut Keys for Faster, More Accurate WorkAdvanced Excel shortcut keys help you navigate large worksheets, edit data in batches, control formulas, and work with…
- Free Savings Tracker Google Sheets: Set Up a Simple SheetYes. You can make a free savings tracker in Google Sheets by starting with a blank spreadsheet, recording…
- Company Contact List Template for ExcelA company contact list template for Excel is a workbook organized so each row represents one person and…
- Freeze Panes in Excel: Keep Rows and Columns VisibleIn Excel for Microsoft 365 on Windows, select the cell immediately below the rows and immediately to the…
- Excel Cell Formatting: How to Test ItIf A2 is formatted as 0.00%, enter =CELL("format",A2)="P2" in another cell to test its number format; Excel returns…
- Excel Cell Service in Clementi: What to CheckIf you mean a mobile-phone repair or service business called Excel Cell Service, I can’t reliably confirm that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.