To edit filter options in Excel, select a cell in your data, choose Data > Filter, then open the arrow in a column heading and select values or criteria. The dropdown’s value list comes from the data itself, so to add or remove listed choices, change the source data; to narrow results without changing it, use the filter criteria.
What You’ll Need
- An Excel workbook with a list of data arranged in columns, with one clear header row and no completely blank rows or columns inside the list.
- Permission to edit the workbook if it is shared or protected. If you plan to change the source values, make a copy first when those changes could affect other calculations or reports.
The steps below use Excel for Microsoft 365 on Windows. Filter menus are similar in Excel for Mac and Excel for the web, although their ribbon layout and some menu wording can vary. On Mac or the web, use the Data tab’s Filter command if a Windows shortcut or exact ribbon location is different.
Step-by-Step
- Check the data range and headers. Make sure each column has one descriptive heading, such as Region or Status, and that each row represents one record. Remove merged cells from the header and avoid blank rows inside the list; either can cause Excel to detect only part of the data. If the data is already an Excel table, its header arrows may already be available. Otherwise, click inside the range and choose Data > Filter. Excel adds a dropdown arrow to each header. On Windows, Ctrl+Shift+L toggles AutoFilter on or off; on Mac, use Data > Filter if that shortcut is unavailable or behaves differently.
- Open the column you want to filter. Click the dropdown arrow beside the relevant heading. The menu usually includes a search box, a list of distinct values, sorting commands, and filter criteria suited to the column’s data type. If the arrow is missing, check that filtering is on and that you selected the intended header row. In Excel for the web or Mac, the same choices are generally opened from the header arrow, even if the menu is arranged differently.
- Choose specific values from the list. In the dropdown, clear Select All, then tick the values you want to keep visible. For example, in a Status column, select Open and In progress to hide completed rows. Click OK or Apply if the menu offers that button. The worksheet now shows matching rows and hides the others; it does not delete them. To find a long value list quickly, type part of a value in the search box, select the matching result or results, and confirm. Check the selection carefully: a search narrows the displayed choices, but it does not necessarily select every matching item automatically.
- Use a custom text, number, or date condition. Open the column arrow and choose the relevant category, such as Text Filters, Number Filters, or Date Filters, then select a condition such as Contains, Greater Than, or Between. Enter the value or values and confirm. For instance, use Text Filters > Contains to show product names containing the word cable. For a numeric column, Number Filters > Greater Than can show values above a threshold. Date options may offer periods such as This Month or a date range. The available criteria depend on the column and how Excel recognizes its contents.
- Combine conditions when one is not enough. In the Custom Filter dialog, select a comparison and enter the first criterion, then choose And or Or before entering a second. And displays rows that satisfy both conditions; Or displays rows that satisfy either condition. For example, filter an amount column for values greater than 100 and less than 500 by using And. To show records from either of two regions, use the value checklist or create suitable alternatives with Or. Avoid mixing up these logic choices, because they can make the result unexpectedly narrow or broad.
- Apply filters to more than one column. Set a filter in one column, then open another column’s dropdown and choose its conditions too. Excel combines filters across columns: a row must meet the active condition in each filtered column to remain visible. For example, filter Region to North and Status to Open to show only open records from the North. A filter arrow may show a filter indicator when a condition is active. Use the dropdowns to review each column’s selections if the results are not what you expected.
- Change or remove a filter. Reopen the filtered column’s dropdown and select different values or criteria, then confirm. To remove only that column’s condition, choose Clear Filter From followed by the column name, or use the equivalent clear option in that menu. To remove all active conditions but keep the dropdown arrows, choose Data > Clear on Windows. This is different from toggling Data > Filter or pressing Ctrl+Shift+L, which turns the filter arrows off. In Mac and web versions, use the available clear command on the Data tab or in the column menu.
- Refresh the results after changing data. If you edit values while a filter is active and the visible rows do not update as expected, use Data > Reapply on Windows. Excel normally updates filters as you edit, but reapplying can help refresh the displayed results after changes. If you add records beyond the filtered range, convert the data to a table with Insert > Table, or adjust the range so new rows are included. Tables generally expand as you add rows and retain filter controls on their headers.
Remember that filtering only changes which rows are displayed. It does not edit the cell values or permanently remove hidden records. If you need to change the choices in the dropdown’s value list, edit the entries in that column; the list is generated from the data in the filtered range.
Common Mistakes
- Expecting to edit the dropdown list directly. The checklist is based on distinct values in the source column. To add a choice, add a corresponding value to the data; to stop an obsolete value appearing, correct or remove it from the records. If you want a controlled entry list for future data entry, that is a separate feature: Data > Data Validation, not the filter menu.
- Filtering only part of the list. Blank rows, blank columns, or selecting a partial range can leave records outside the filter. Check the first and last rows included, and consider converting a continuous dataset to a table.
- Confusing sorting with filtering. Sort A to Z or Smallest to Largest changes row order; it does not hide rows. Use the filter criteria or value checklist when you want to narrow what is displayed.
- Assuming hidden rows are deleted. A filter hides nonmatching records temporarily. Before copying, deleting, or calculating on filtered data, confirm which rows are visible and whether the command affects hidden rows as well.
- Using the wrong data type or condition. Numbers stored as text, inconsistent date formats, extra spaces, or spelling variations can produce unexpected choices and results. Standardize the source values when appropriate, then clear and reapply the filter.
Tips
- Turn a well-formed range into a table with Insert > Table. Confirm that My table has headers is selected. Tables provide filter arrows automatically and are easier to expand when adding new records.
- Use the search box for long lists, but clear the search before reviewing the complete checklist. It can be easy to forget that the menu is showing only search matches.
- Filter by cell color or font color from the column menu when the source data uses consistent formatting and the menu offers Filter by Color. Color is useful for reviewing marked records, but a text status column is often clearer and more dependable for ongoing workflows.
- To return to a full view without removing the filter controls, clear the active conditions. This makes it easier for another person to understand that the list is filterable.
- When several people use the workbook, document important filter criteria in a nearby note or report title. A filtered view can otherwise be mistaken for the entire dataset.
Troubleshooting
The filter arrows do not appear. Click inside the data and choose Data > Filter. If Excel filters only some columns or rows, check for blank rows or columns splitting the list, merged headers, or a selection that excludes part of the range. Remove the interruption or select the full range before turning on filtering. If the worksheet is protected, ask the workbook owner whether filtering is allowed.
A value is missing from the dropdown. Check that the record containing it is inside the filtered range and that the value is not hidden by another active filter. Clear the dropdown’s search text, clear other column filters temporarily, and inspect the source cell for leading or trailing spaces or a spelling difference. If new rows were added outside the range, expand the range or use a table that includes them.
The results are empty or surprising. Reopen each active filter and verify the selected values and conditions. Check whether a custom filter uses And when you intended Or, or the reverse. Also confirm that numbers are stored as numbers and dates are recognized as dates; inconsistent formats may be treated as text. Clear the filters, standardize the data if needed, and apply the criteria again.
Changes do not show in the results. Use Data > Reapply, and verify that the edited records remain within the range. If a filter seems stuck, clear the relevant column’s filter and set it again. Avoid deleting rows merely because they are hidden; clear filters first if you need to inspect the complete list or make a broad change.
Frequently Asked Questions
Can I type a new option into an Excel filter dropdown?
No. The value checklist is generated from entries already present in the column. Add the value to the source data if it should appear as a filter choice. Data Validation can restrict future entries, but it does not add options to a filter’s list by itself.
How do I show all rows again?
Open a filtered column’s arrow and choose Clear Filter From that column, or use Data > Clear to clear all filter criteria in the Windows desktop app. The arrows can remain on so you can filter again later.
Can I filter by more than one value in the same column?
Yes. Select multiple entries in the dropdown checklist, such as two regions. For more complex conditions, choose the appropriate Text, Number, or Date Filters menu and use the custom criteria dialog when available.
Why does Excel show duplicate-looking choices?
Values that look alike may differ because of extra spaces, punctuation, spelling, or underlying number and date formats. Inspect and standardize the source cells if the distinction is accidental. Excel’s filter list reflects the actual values, not just how similar they look on screen.
Do filters delete or change my data?
No. A normal filter hides rows that do not meet the criteria; it does not delete them or change their contents. Clear the filter to show those rows again. Take care with separate actions such as deleting visible rows, which can permanently change the workbook.
Are filters the same in Excel for Mac and the web?
The basic process is similar: use the Data tab to turn on filtering, then use a header arrow to select values or conditions. Menus and some available options can differ by version, so follow the commands displayed in your app if their wording or placement differs from Windows.
Related
- How to Delete in Excel Using the Keyboard
- Excel Delete Shortcut: Clear Cells, Rows, and Columns
- How to Delete a Header in Excel Without Removing Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Growth Analysis: Formulas, Trends, and ExamplesExcel growth analysis measures how a value changes over time. For two consecutive values in B2 and B3,…
- Excel Length of Service in Years and Months: FormulasTo calculate completed length of service in years and remaining months, enter the start date in A2 and…
- How to Filter Out Weekend Dates in Excel: Step-by-StepTo filter weekend dates out of an Excel list, add a helper column and enter =WEEKDAY(A2,2)>5 beside the…
- Is INDIRECT Volatile in Excel? Performance ExplainedYes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named…
- Excel RIGHT, LEFT, and MID Formulas: Syntax and ExamplesIn Excel, RIGHT extracts characters from the end of text, LEFT extracts them from the beginning, and MID…
- XLOOKUP Match Mode Examples: Exact and ApproximateXLOOKUP’s match_mode argument controls what counts as a match: use 0 for an exact match, -1 for an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.