To add a filter in Excel for Microsoft 365 on Windows, click a cell in your data, choose Data > Filter, then open the arrow in the column header and select the values or conditions to show. Excel filters the rows in the data range, so you can focus on matching records without deleting anything.
What You’ll Need
- An Excel workbook containing a list or table, such as a contact list, inventory, or sales records.
- A header row with a clear name for each column, such as Date, Region, or Status. Filters work best when each column has one header and the data has no completely blank rows or columns splitting it into sections.
- Permission to edit the workbook. If it is read-only or protected, you may need to save an editable copy or ask the workbook owner to change its protection settings.
You do not need to convert the range to a table to use filters. Excel can add filter arrows to an ordinary data range. A formatted Excel table includes filtering automatically, however, and can be a convenient choice for data you expect to keep expanding.
Step-by-Step
- Check that the data is laid out as a list. Put one field in each column and place a descriptive heading at the top of each column. For example, a stock list might have Product, Category, Quantity, and Supplier in row 1, with one product per row underneath. Remove completely blank rows or columns within the list, since they can cause Excel to treat adjoining data as a separate range. Avoid merged cells in the header row.
- Select a cell in the data. Click any populated cell in the list, including a header or a data cell. If the data is one uninterrupted block, Excel can usually identify the range for you. If the worksheet contains nearby blocks or notes that could confuse the selection, select the full range deliberately, including the header row and all records, before turning on the filter. Do not select only one column if you need the other fields in each record to remain together.
- Turn on the filter controls. On the Windows ribbon, choose Data > Filter. Excel adds a small drop-down arrow to each header in the detected range. You can also toggle AutoFilter with Ctrl+Shift+L on Windows. On a Mac, use Data > Filter from the ribbon; shortcut availability can vary with keyboard settings. In Excel for the web, select a cell in the list and choose Data > Filter.
- Open the arrow for the column you want to filter. For instance, to show only orders marked Pending, open the arrow in the Status header. The arrow menu belongs to that field, but the filter affects complete rows in the list: Excel hides records that do not meet the condition and keeps the values in the other columns aligned with the matching records.
- Choose the values or condition to keep. For a basic filter, clear (Select All), select the value or values you want to see, and click OK. To show several regions, for example, select both East and West. If the list is long, use the search box in the menu to find a value before selecting it. The choices shown are based on values in that column, not values in the entire worksheet.
- Use a condition when selecting individual values is not practical. Open the column arrow and point to the relevant filter option. Text columns can offer Text Filters, number columns can offer Number Filters, and date columns can offer Date Filters. Choose a condition such as Contains, Greater Than, or a date period, enter the requested value, and confirm. The available choices depend on the data Excel recognizes in that column. For example, to find quantities above 20, use a number condition rather than selecting every quantity one by one.
- Check the filtered results. Look at the visible records and the column arrow to confirm the filter is active. Excel hides nonmatching rows; it does not remove them. The row numbers may skip, and the status bar may show information about the selected visible cells. If nothing appears, reopen the arrow and check that the chosen values or conditions actually match the data.
- Clear or adjust the filter when you are done. To restore all records while keeping the filter arrows, open that column’s arrow and choose Clear Filter From followed by the column name, where available. You can also open its menu, select (Select All), and click OK. To remove the arrows from the range, choose Data > Filter again. Turning the arrows off is different from clearing a filter: clearing restores rows but leaves the controls available for later use.
For data that grows over time, you can instead make it an Excel table: select a cell in the range and choose Insert > Table, confirm the range and whether it has headers, then click OK. The table displays filter arrows in its header row. Check that My table has headers is selected when the first row contains field names; otherwise Excel may treat your headings as data and create generic column names.
Common Mistakes
- Selecting just the target column: Filtering one column should usually mean choosing a condition for that field while keeping each record intact. If you apply a filter to an isolated column instead of the whole list, values in other columns may not be included in the filtered range. Start by clicking within the complete list, or select the whole rectangular range.
- Leaving a blank row inside the data: A fully blank row can make Excel detect only the block above it. Remove the separator row, or select the full intended range before choosing Data > Filter. A blank cell in an otherwise populated row is different; that row remains part of the list.
- Having duplicate, missing, or multi-row headers: Each field should have one distinct, nonblank heading in a single header row. Duplicate headings make it harder to tell which arrow belongs to which field, and a title row above the headers can be mistaken for part of the data if the range is selected incorrectly.
- Thinking hidden rows have been deleted: A filter hides records that do not match. It does not erase them. Clear the filter to display them again. Before copying or deleting data, confirm whether you intend to work with only visible rows or with the complete list.
- Expecting a filter to change only one cell or field: The drop-down is in one column, but a row that does not match is hidden across the range. This is necessary to keep each record together. Filtering is not the same as hiding one column, sorting values, or deleting unwanted records.
- Assuming similar-looking values are identical: A value with a trailing space, a different spelling, or a different data type may appear as a separate choice. Dates stored as text may not behave like real Excel dates, and numbers stored as text may not sort or filter as expected.
Tips
- Use search for long lists of unique values. If a column contains many product names or email addresses, open its arrow and search for the item instead of scrolling through the checklist. Clear the search or change the selection when you need a different result.
- Combine filters across columns. You can filter by Region and then by Status, for example, to show only East-region orders that are Pending. Each additional filter narrows the visible rows; clear individual filters to broaden the results again.
- Choose the filter type that matches the information. Use Text Filters for words, Number Filters for quantities or prices, and Date Filters for actual dates. For dates, Excel may group choices by year, month, or day. If the menu offers unexpected choices, check whether the column mixes text, numbers, dates, and blanks.
- Freeze the header row for a tall list. Choose View > Freeze Panes > Freeze Top Row when the headers are in row 1. This keeps headings visible while you scroll through results. Freezing panes changes the view, not the filter range or the underlying data.
- Keep the range tidy as you add records. Excel tables generally expand when you type directly beneath them or add a row at the end, and their filter controls remain attached to the table. With an ordinary range, confirm that newly added rows are included before relying on the filter.
- Distinguish filtering from sorting. Sorting changes the order of records; filtering temporarily hides records that do not meet a condition. A column menu may offer both, so choose Sort only when you want to rearrange the visible data.
Troubleshooting
- No filter arrows appear: Click inside the intended range and select Data > Filter. Check whether the worksheet is protected, whether you are editing a cell, or whether another filter is already active. If Excel selected too little data, undo the command if needed, select the complete range including headers, and apply Filter again.
- Some records are missing from the filter range: Look for blank rows or columns that split the data, then select the full range manually and turn on the filter. If the data is an Excel table, check whether the new records were added outside the table rather than in its next row.
- The filter menu does not show the value you expect: Clear filters on other columns first, since a filter in another field may already hide the relevant row. Then inspect the source cells for spaces, inconsistent spelling, or a value stored in a different format. Editing and standardizing the source values may be necessary before filtering reliably.
- A date or number condition behaves like a text filter: Check a few source cells and correct entries stored as text. For dates, use consistent real date values; for quantities, convert text entries to numbers. Reopen the filter menu after correcting the data so Excel refreshes the available values and conditions.
- The command is unavailable or results are confusing in a shared workbook: Confirm that you have editing permission and that another user or view is not affecting the sheet. In Excel for the web, a coauthor may have a different personal sheet view. Ask the workbook owner about protection or collaboration settings if you cannot change the filter.
- You want to see every row again: Clear the filter in each filtered column. If the arrows remain but all records are visible, the filters are cleared. If the arrows disappear, choose Data > Filter to turn them back on; reapplying the controls does not delete the data.
Frequently Asked Questions
Can I filter only one column in Excel?
Yes. Open the arrow in the column you want and choose values or a condition. For a normal list, apply the filter to the complete data range; Excel hides whole nonmatching rows so that the other fields in each record stay together.
Does adding a filter change or delete my data?
No. A filter changes which rows are visible, not the stored values. Clear the filter to show hidden records again. If you edit or delete visible cells while filtered, however, those edits do change the workbook.
Can I filter by more than one column at once?
Yes. Apply a filter in one column, then open another column’s arrow and select its values or condition. Excel shows rows that satisfy the filters currently applied across the list. Clear a filter in any column to remove that part of the restriction.
How do I filter for blank cells?
Open the relevant column’s arrow and select (Blanks) if that option appears in the checklist. To show nonblank entries, select the other values instead. A cell containing a space is not truly blank, so remove stray spaces if the results do not match what you expect.
How do I remove the filter without losing the data?
Choose Data > Filter to toggle the filter arrows off. This removes the controls, not the records. To keep the arrows but show all records, clear the active filter instead.
Do the steps differ on Mac or in Excel for the web?
The basic method is similar: select a cell in the data, choose Data > Filter, and use the header arrow. The ribbon layout and keyboard shortcuts can differ by platform and settings, so the menu path is the reliable option when a shortcut does not work.
Related
- Excel Yellow Color Code: RGB, Hex, and Theme Values
- ISBLANK in Excel: How It Works and When to Use It
- Excel Forecasting: How to Predict Future Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter by Color in ExcelTo filter by color in Excel for Microsoft 365 on Windows, turn on filters for your data, open…
- INDEX Excel Arguments Explained: Syntax and ExamplesIn Excel, INDEX returns a value or reference at the intersection of a specified row and column in…
- Excel Payment Tracker: Setup, Formulas, and Practical TipsAn Excel payment tracker is a worksheet for recording what is owed, what has been paid, when payments…
- Excel Find and Replace Paragraph Mark: The Right MethodIn Excel for Microsoft 365 on Windows, press Ctrl+H, click the Find what box, and press Ctrl+J to…
- Excel Formula for Time Clock Hours In and Out by ShiftFor clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.