How to Add Filter Options in Excel: A Step-by-Step Guide

Formulas & functions · Updated November 20, 2026

To add filter options in Excel for Microsoft 365 on Windows, select a cell in your data, then choose Data > Filter. Excel adds a drop-down arrow to each header so you can show selected entries, search values, sort rows, or apply text, number, and date conditions. Filtering hides rows temporarily; it does not delete them.

What You’ll Need

  • An Excel workbook containing a list or table, with one clearly labeled header row and one record per row.
  • A range without completely blank rows or columns splitting the data. Blank cells inside a column are fine.
  • Permission to edit the worksheet. If the sheet is protected, its owner may need to allow filtering.

The steps below use Excel for Microsoft 365 on Windows. In Excel for Mac and Excel for the web, the Data tab also has a Filter command, though ribbon layout can differ. On Mac or the web, use the ribbon command if a Windows shortcut does not work.

Step-by-Step

  1. Prepare the data. Check that the first row of the list contains a distinct heading for every column, such as Date, Customer, Region, and Amount. Put each record on its own row and keep each kind of information in its own column. For example, avoid combining a customer’s name and email address in one cell if you may want to filter by either. Remove completely blank rows or columns within the list, because they can cause Excel to recognize only part of the range. Make sure the list does not have merged cells in its header or data area; merged cells can interfere with sorting and filtering.
  2. Select a cell in the list. Click any populated cell within the data range. Excel will usually detect the surrounding contiguous range automatically when you turn on filtering. If the worksheet contains nearby tables or unrelated notes, select the intended range explicitly, including its header row, before moving on. Do not select just the header row: Excel needs the records below it to create useful filters.
  3. Turn on the filter arrows. Choose Data > Filter on the ribbon. Alternatively, on Windows, press Ctrl+Shift+L to toggle AutoFilter for the selected range. Each recognized header should now show a small arrow. On Mac, use Data > Filter if the shortcut differs on your keyboard or is intercepted by another command. To remove the arrows later, choose Data > Filter again, but first clear any filters if you want all rows visible.
  4. Open a column’s filter menu. Click the arrow in the header of the column you want to narrow. The menu normally includes a search box, a checklist of values, sorting commands, and filter choices appropriate to the column’s contents. The checklist is useful when you want a few specific items, such as two regions. Type a word or number into Search to find matching checklist entries, select or clear the relevant checkboxes, and click OK. The search box finds values in the menu; it does not by itself apply a filter. You still need to select the matching value or values and confirm.
  5. Apply a text, number, or date condition. For a text column, open its arrow and choose Text Filters, then select a condition such as Contains, Begins With, or Equals. Enter the comparison text in the dialog and click OK. For numeric data, use Number Filters to set conditions such as greater than, less than, or between two values. For dates, use Date Filters to choose a date period or a condition such as before or after a date. The menu options depend on how Excel recognizes the column; a column containing mixed text and numbers may not offer the expected number filters. Use a consistent data type in each column.
  6. Set two conditions when needed. Some filter dialogs let you combine criteria. For example, to show amounts between 100 and 500, choose the appropriate greater-than-or-equal-to condition for 100 and less-than-or-equal-to condition for 500, joined with And. Use Or when either condition should qualify. For text, a custom condition could show entries that contain North or South. Check the operator carefully: And means a record must meet both conditions, while Or means it can meet either one. The exact dialog wording varies with the type of filter you chose.
  7. Filter by more than one column. Apply a filter in one column, then open another column’s arrow and choose its criteria. Excel shows rows that satisfy the active filters together, so a Region filter plus a Status filter narrows the list to records meeting both selections. For example, choosing West in Region and Open in Status shows only open records from the West. You can continue adding filters to other columns; the funnel or filter indicator on a header signals that a filter is active there. If the result is empty, review each active condition because the combination may match no records.
  8. Review, change, or clear the result. Look at the row numbers and the filter indicators to confirm that the list is narrowed as intended. To change one column’s criteria, open its arrow and adjust the checkboxes or condition. To remove only that column’s filter, open its menu and choose Clear Filter From [column name]. To remove the worksheet’s active filters, select Data > Clear; this clears filter criteria so the rows are shown again. Clearing criteria is different from turning off the arrows with Data > Filter. If you have sorted the data too, check the displayed order separately before continuing your work.
  9. Consider converting a recurring list to a table. If you regularly add records, select a cell in the range and choose Insert > Table, confirm the range, and ensure My table has headers is selected when appropriate. A table includes filter arrows by default and generally expands as you type or paste data directly below it. If the list already has filter arrows, converting it is optional. Tables are especially handy for ongoing logs, inventory, or sales lists, but they are not required for a one-time filter.

Common Mistakes

Including an incorrect range: Selecting only part of a list can leave records outside the filter. Select a cell inside the complete, connected range or explicitly highlight the entire range with its headers. When the filtered range is wrong, turn the filter off, select the correct range, and turn it back on.

Using blank or duplicate headers: Give each column a clear, nonblank heading. Duplicate labels can make menus confusing, and a blank header can make it difficult to identify a column. Avoid merged header cells because they can complicate the range structure.

Expecting a search to filter automatically: Typing into the filter menu’s search field only narrows the choices displayed in that menu. Select the desired results and click OK to apply them.

Assuming hidden rows were deleted: A filter hides rows that do not match; it does not remove their contents. If you copy or edit a filtered list, check what is visible and avoid treating the displayed subset as the full dataset unless that is your intention.

Mixing formats in one column: Dates stored as text, numbers with words, or inconsistent spellings can make filter options behave unexpectedly. Standardize entries before relying on a date or number condition.

Tips

  • Use descriptive, concise headers, such as Invoice Date rather than Date if the worksheet has several kinds of dates.
  • Use the checklist for a small set of exact values and a condition such as Contains or Greater Than for a broader rule.
  • To sort as well as filter, use the sort commands in the header menu. Sorting changes row order; filtering changes which rows are displayed. Both can be active at the same time.
  • For colors, open the column menu and look for Filter by Color when cells or font in that column have color formatting. Color filtering is not a substitute for consistent values such as Approved or Pending.
  • When adding new data to a normal filtered range, check that the filter range includes the new rows. A formatted Excel table is often easier to maintain for lists that grow regularly.
  • If you share the workbook, explain which filters are active. Another person may open the same sheet with the current filtered view and mistake it for the complete list.
  • Use Data > Clear when you want to reveal all rows while keeping the filter arrows available for later use. Use Data > Filter to toggle the arrows themselves.

Troubleshooting

No arrows appear: Confirm that you selected a cell inside the data, then choose Data > Filter. Check whether the worksheet is protected or whether the range has merged cells. If you selected a range manually, include the heading row and the records. If arrows were previously switched off, selecting Filter again should restore them.

Some rows are missing from the filter range: Turn filtering off, inspect the list for a fully blank row or column, and select the complete range again. A nearby note or second dataset may have caused Excel to detect a smaller region than you intended. For a growing list, consider converting the range to a table and confirm that new rows are being added inside the table.

The menu shows unexpected options: Excel chooses Text Filters, Number Filters, or Date Filters based on the values it recognizes. Look for numbers or dates stored as text, leading or trailing spaces, and inconsistent entries. Correct the source values, then reopen the menu. For a date column, make sure the entries are actual Excel dates rather than strings that only look like dates.

The filter returns no rows: Open each column with an active filter and verify its selections. Multiple column filters are combined, so criteria that work individually may have no records in common. Also check spelling, extra spaces, date boundaries, and whether a numeric value is stored as text. Clear one filter at a time to identify which condition is excluding the records.

You cannot see all records again: Use the affected header’s menu and choose Clear Filter From its column name, or choose Data > Clear to clear active filter criteria. If the arrows are gone, turn them on again with Data > Filter. Remember that manually hidden rows or a separate sort may also affect what you see; clearing a filter does not necessarily reverse a sort or unhide rows hidden manually.

The Filter command is unavailable: Check whether the worksheet is protected, whether you are editing a cell, and whether the workbook opens in a restricted or read-only state. Press Esc to leave cell editing and check the Review tab for sheet protection. The workbook owner may need to change protection settings or grant editing access.

Frequently Asked Questions

Can I filter by several values in one column?
Yes. Open that column’s drop-down, select the checkboxes for each value you want to keep, and click OK. The displayed rows can match any of the selected values in that column.

Can I filter two or more columns at once?
Yes. Apply a filter in each column in turn. Excel displays rows that meet the active criteria across the columns, so each additional filter narrows the current result.

Does adding a filter change or delete my data?
No. A filter hides rows that do not meet the criteria; their values remain in the worksheet. Clearing the filter makes the rows visible again.

How do I filter for blank cells?
Open the column’s filter menu and select (Blanks) in the checklist if Excel offers it. To show nonblank entries instead, clear that option and select the values you want. The label may not appear if there are no blank cells in the detected range.

Can I filter a list without converting it to a table?
Yes. Select a cell in the range and choose Data > Filter. A table is optional, although it can make a growing list easier to manage because it includes filter arrows and can expand with new entries.

Why do my filters disappear when I add new rows?
The new rows may be outside the filtered range. Extend the range to include them, or convert the list to an Excel table and add records directly below it. Check the table or range boundaries before relying on filters for a complete report.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.