To filter purchases by date in Excel, select your purchase list, choose Data > Filter, open the arrow in the purchase-date column, then choose a date filter or select the dates you want. This shows only matching transactions without deleting the other rows. Before you start, make sure the column contains real Excel dates rather than text that only looks like dates.
What You’ll Need
- An Excel worksheet with one header row and a column for purchase dates.
- Microsoft Excel for Microsoft 365 on Windows. The same basic filtering steps are available in Excel for Mac and Excel for the web, though ribbon placement can vary slightly.
- Optional: a copy of the workbook if you want to preserve the original view or are still learning how the filter behaves.
Step-by-Step
- Check the purchase-date column. Each transaction should have a date in its own cell, with a clear header such as Purchase Date. Keep the header in the first row of the list, and avoid blank rows or merged cells within the data. If a cell also contains a time, such as 3/14/2026 2:30 PM, it is a date-time value; see the timestamp note below if you need to filter to an exact calendar day.
- Confirm Excel recognizes the entries as dates. Click a date cell and look at the formula bar. A genuine Excel date is stored as a number, even if it displays in a familiar date format. You can change its appearance using Home > Number Format, but formatting alone does not turn text into a date. If you are unsure, try setting a test cell to Short Date and check whether it displays as a date; a text entry may stay unchanged or behave differently.
- Select the list or click inside it. Click any cell in the purchase data. Excel will usually detect the full contiguous list automatically. If the data has blank rows, separate blocks, or extra columns that should not be filtered, select the complete rectangular range yourself, including its header row. Do not include unrelated notes or totals below the list unless those rows are part of the records.
- Turn on filtering. On Windows, go to Data > Filter. You can also use Home > Sort & Filter > Filter. Excel adds a drop-down arrow to each column header in the selected range. On Mac, use Data > Filter; in Excel for the web, use the Data tab and choose Filter. If the arrows appear on only part of the list, turn the filter off, select the full range, and turn it on again.
- Open the purchase-date filter. Select the arrow in the Purchase Date header, not an arrow in a different field such as Vendor or Amount. Excel shows the available date values and filter commands. If Excel recognizes the values as dates, the menu includes date-specific options, often under Date Filters. The available labels can vary a little with the data and Excel version.
- Choose a date filter or individual dates. To show a period, choose Date Filters and then a suitable option, such as Between, Before, After, or a period option such as This Month, when available. Enter or select the requested date or dates in the dialog, then confirm. To show selected individual dates instead, clear Select All in the value list and tick the dates you need. You can expand or collapse year and month groups where Excel presents dates hierarchically.
- Set the period carefully. For a range, check both endpoints before you confirm. For example, to review purchases from April 1 through April 30, use the Between option and enter April 1 as the start and April 30 as the end. Between generally includes both boundary dates. If the dates are in a different year, include the year in each entry so Excel does not apply the wrong year.
- Review the filtered rows. Excel hides rows that do not meet the condition; it does not remove them. Check the visible dates and, if useful, compare the status-bar count or a total with what you expect. The filter arrow usually shows a filter indicator while the column is restricted. To combine conditions, apply another column filter—for example, choose a date period and then a particular supplier.
- Clear or change the filter when finished. Open the Purchase Date arrow and choose Clear Filter From Purchase Date, or use Data > Clear to clear filters on the sheet. The rows reappear. To change the period, open the arrow again and select a different condition. To remove the filter arrows entirely, choose Data > Filter again; that turns filtering off but does not delete the records.
Filtering dates that include times: A value such as 4/30/2026 3:15 PM contains both a date and a time. If your filter does not return all transactions from the intended day, add a helper column that extracts the date portion. With the timestamp in B2, enter =INT(B2) in the helper column and fill it down. For example, if B2 is 3/14/2026 2:30 PM, the formula returns the date serial for March 14, 2026; format the helper cells as dates using Home > Number Format, then filter that helper column. Keep the original timestamp column if you need the time for auditing.
Common Mistakes
- Dates are stored as text. Text can look exactly like a date but may not appear in date-specific filter options or sort in calendar order. A format change does not convert it. If the data came from a CSV or another system, use Data > Text to Columns on a copy or selected date column, choose the correct date order in the column data format, and finish the conversion. For example, choose MDY only when the source means month/day/year; using the wrong order can swap month and day.
- The selected range is incomplete. If you selected only the date column, the filter might separate dates from the matching purchase details. Click inside a continuous list before turning on Filter, or select all columns and rows in the list, including headers.
- A blank row splits the records. Excel may treat data below a fully blank row as a separate block when it detects the range. Remove unintended blank rows or select the full range manually before applying the filter.
- The year was overlooked. A date such as 5/10 appears similar across multiple years. When selecting a date or entering a range, confirm the year, especially if the data covers more than one calendar year.
- The filter is mistaken for deletion. Filtered-out transactions are hidden, not erased. If a total changes, check whether the total is designed to include only visible rows or all rows before concluding that records are missing.
- A filter from another column is still active. A date range can be correct but show no rows because another filter also limits the list. Check the header arrows and clear other filters temporarily to diagnose the result.
Tips
- Convert the list to an Excel table. Click inside the data and choose Insert > Table, confirm that My table has headers is selected, and choose OK. Tables add filter arrows and expand more reliably when you append new purchase rows. Filtering still works without a table.
- Use date periods for recurring reviews. Options such as This Month, Last Month, This Quarter, or This Year, when offered, are useful for quick checks. For a fixed audit period, enter explicit start and end dates instead, so the intended year and boundaries are easy to verify.
- Sort after filtering if it helps. Use the date header arrow to sort oldest to newest or newest to oldest. Sorting changes row order, while filtering controls which rows are visible; neither operation deletes records.
- Keep a clear source format. For imported purchases, use one consistent date convention and avoid mixing entries such as 04/05/2026 and 5 Apr 2026 if their meaning could be ambiguous. Confirm the source system’s date order before converting imported text.
- Use a separate helper column for analysis. A date-only helper can make timestamp records easier to filter without losing the original time. Label it clearly, such as Purchase Day, and avoid overwriting the source timestamp.
- Know the shortcut if you use it often. On Windows, Ctrl+Shift+L toggles AutoFilter for the current data range. On Mac, the equivalent shortcut is Command+Shift+F in Excel for Mac versions that support it; if it does not work in your setup, use Data > Filter. The ribbon command is the clearest option when you are unsure.
Troubleshooting
Date Filters does not appear: Excel may be treating the entries as text, or the selected column may contain mixed data. Check several cells, correct the date order if necessary, and convert the column to real dates. Then turn the filter off and on again. If conversion is not practical, clean the source data before filtering rather than relying on text values that happen to look like dates.
The filter shows no matching purchases: Confirm that the start and end dates include the intended year and that the range is not reversed. Clear filters on other columns, then try a broader period. Also inspect a few underlying cells for hidden times or text values; imported timestamps and text dates can behave differently from plain date values.
Some transactions on a date are missing: Check whether the records have times attached. If so, create the date-only helper column with =INT(B2), assuming B2 contains an Excel date-time value, fill it down, format it as a date, and filter the helper column. If the formula displays an error, verify that the source cell is a numeric Excel date-time rather than text.
Rows outside the list are not filtered: The filter may have been applied to a partial range. Turn it off, select the entire purchase table including the header row, and choose Data > Filter again. Consider converting the data to a table so new rows are included more consistently.
The dates sort in an unexpected order: This usually indicates text dates or mixed date formats. Convert the source values using the correct date order, verify the results, and then sort or filter again. If the source includes ambiguous dates, check the original file or system rather than guessing whether a value is month/day or day/month.
Frequently Asked Questions
Can I filter purchases between two dates?
Yes. Open the date-column arrow, choose Date Filters > Between, enter the start and end dates, and confirm. Check the year and whether the endpoints should be included.
Can I filter by month without entering every date?
Yes. Use the date list’s year and month groups, if shown, or choose a period command such as All Dates in the Period. You can select the relevant month and year without ticking each day individually.
Does filtering delete purchases that are not shown?
No. Excel hides rows that do not meet the filter conditions. Choose Clear Filter or clear the sheet’s filters to show the full list again.
Why does Excel show ordinary checkboxes instead of date filters?
The date column may contain text, blanks, or mixed types. Convert the entries to genuine Excel dates, check the source date order, and reapply the filter.
Can I filter by date and supplier at the same time?
Yes. Apply the date filter, then open the supplier column’s arrow and select the supplier. Excel displays rows that satisfy both conditions; clear either column’s filter to broaden the results.
Will the filter include purchases added later?
If you used an Excel table, new rows added directly below it are generally included in the table and its filters. With a regular range, you may need to extend the filtered range or reapply the filter after adding rows.
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
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
- Lower Quartile Formula in Excel: Find Q1 CorrectlyIn Excel, calculate the lower quartile (Q1) with =QUARTILE.INC(A2:A9,1), replacing A2:A9 with your data range. This returns the…
- IF vs. IFERROR in Excel: Key Differences and ExamplesIF tests a condition and returns one result when it is true and another when it is false;…
- Excel Copy Data Validation to Other Cells the Right WayTo copy only a cell’s data validation in Excel for Microsoft 365 on Windows, copy the cell, select…
- Excel Reverse HLOOKUP: Formulas and Better AlternativesExcel has no separate “reverse HLOOKUP” function. If you mean looking up a value in one row and…
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.