How to Get the Filter Option in Excel and Use It Easily

To get the Filter option in Excel, click a cell in your data and select Data > Filter. Excel adds a drop-down arrow to each column heading; use an arrow to show only rows that match the values or conditions you choose. Make sure your data has one clear header row and no completely blank rows splitting the list.

What You’ll Need

Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The main steps are similar across these versions.
A worksheet containing a list of data, with a heading at the top of each column, such as Date, Product, and Amount.
Permission to change the worksheet. A protected sheet may prevent you from applying or changing filters.

Step-by-Step

Check that the data is arranged as a list. Each column should contain one type of information, with a single heading in its first row. For example, a sales list might have Date, Region, Product, and Units as its headings. Keep each record on one row. Remove merged cells from the heading row, and avoid blank rows or columns inside the list because they can make Excel detect only part of your data.
Click inside the data. Select any cell in the list, including a cell below the headings. If the data has no empty rows or columns splitting it, Excel will usually recognize the complete range automatically. If your worksheet has surrounding content or gaps, select the exact range you want to filter, including the heading row, before continuing.
Turn on Filter. On Windows, choose Data > Sort & Filter > Filter. You can also use Home > Editing > Sort & Filter > Filter. The Windows keyboard shortcut is Ctrl+Shift+L. In Excel for Mac, choose Data > Filter; the shortcut is commonly Command+Shift+F, but the menu is the reliable option if your keyboard settings differ. In Excel for the web, select Data > Filter on the ribbon.
Confirm that the arrows appear. A small drop-down arrow should now appear in each heading cell in the selected range. If Excel put an arrow on a data row instead of a heading, turn the filter off, select the correct range including the heading row, and apply Filter again. A heading should be a label, not a blank cell or a value that belongs in the records.
Open a column’s filter menu. Select the arrow in the heading for the information you want to narrow down. For a list of text or numbers, the menu normally shows available values with checkboxes. Clear Select All, then select the items to keep. You can also use the search box in the menu to find a particular value. Select OK or Apply, if shown, to display the results.
Use a condition when choosing individual values is not enough. Depending on the column’s contents, choose a command such as Text Filters, Number Filters, or Date Filters. For example, a Number Filters condition can show values greater than a threshold, and a Date Filters condition can show dates in a particular period. Pick the condition, enter the value or dates in the dialog, and confirm. The available commands depend on the kind of data Excel recognizes in that column.
Filter more than one column when needed. Apply a filter in another heading to narrow the visible rows further. For instance, you could show records for the West region and then show only one product within those results. Filters on different columns work together: a row must meet the active conditions to remain visible. The hidden rows are not deleted; they are simply excluded from the current view.
Clear or turn off the filter. To restore a column’s full list, open its drop-down and choose Clear Filter From followed by the column name, or select the relevant clear command in that menu. To clear filters across the range, use Data > Sort & Filter > Clear. To remove the arrows and switch filtering off, select Data > Sort & Filter > Filter again, or press Ctrl+Shift+L on Windows. Turning the feature off does not delete your data.

You can also make a formatted Excel table. Select a cell in the list and choose Insert > Table, check that the selected range is correct, and confirm My table has headers if the first row contains headings. Tables display filter arrows automatically and usually expand when you add rows directly beneath them. This can be convenient for a list that will grow over time, but it is not required to use the Filter option.

Common Mistakes

Selecting only one column by accident. If you select a single column before applying Filter, Excel may apply the arrows to just that selection. Click within the full list to let Excel detect it, or select all columns and the heading row yourself.
Using a blank or duplicate heading. Give each column a clear heading, such as Customer or Order Date. Blank headings, merged heading cells, and repeated labels can make a list harder to filter and can lead to unexpected results, particularly when converting the range to a table.
Expecting filtering to erase unwanted rows. A filter hides rows that do not match; it does not remove them. If you copy filtered results, check that you are copying only the visible cells when that matters, and clear the filter when you want to see the complete list again.
Confusing filtering with sorting. Sorting changes the order of the rows; filtering temporarily limits which rows are displayed. You can use both, but applying a filter does not automatically arrange the remaining values in a particular order.
Leaving a blank line inside the list. A completely blank row can cause Excel to treat the data above and below it as separate regions. Remove the gap or select the full range explicitly before applying Filter.

Tips

Use consistent data formats. Store dates as actual Excel dates, and keep numbers as numbers rather than mixing them with text such as units in the same cells. Consistent values make the correct date, number, or text filter commands more likely to appear.
Use the search box for long lists. If a filter menu contains many unique names or products, search for the value instead of scrolling through every checkbox. Review the checked items before applying the selection.
Check which columns are active. A filter icon can indicate that a column is filtered. If rows appear to be missing, inspect every column’s menu; another active filter may be narrowing the results too.
Keep headings visible while scrolling. For a long worksheet, use View > Freeze Panes > Freeze Top Row to keep the heading row on screen. This does not change the filter, but makes it easier to identify the columns.
Consider a table for regularly updated data. Tables help keep the range together as you add records, and provide filter arrows by default. Make sure new rows are added within or immediately below the table so Excel includes them as expected.

Troubleshooting

The Filter command is unavailable or greyed out. Check whether the worksheet is protected. If you have permission, use Review > Unprotect Sheet and enter a password if prompted. If the workbook is in a shared or restricted state, your access may also limit changes. Save a copy or ask the workbook owner before changing protection or shared-workbook settings.

No arrows appear after you select Filter. Click a cell inside the list and try Data > Filter again. Confirm that you selected the actual data range, not an empty cell nearby. If the ribbon is collapsed, expand it or use the search box at the top of Excel to find the Filter command. In some layouts, the command is under Home > Editing > Sort & Filter.

Only some columns have arrows, or filtering stops at the wrong row. Turn Filter off, remove any blank rows or columns interrupting the list, and select the complete rectangular range from the first heading to the last record. Then turn Filter back on. Check that the headings are in one row and that the range does not include unrelated notes or totals.

A value you expect is missing from the menu. First clear filters in other columns, since they may hide the row containing that value. Then check spelling, extra spaces, and data formats in the source cells. Dates or numbers stored as text may be grouped or offered different filter choices from genuine dates or numbers. If the data has just changed, reapply or clear and reapply the filter so the displayed results reflect the updated list.

The arrow is there, but the results look wrong. Open the column’s menu and check which values are selected and whether a custom condition is active. Also check for multiple filters on other columns. Choose Data > Sort & Filter > Clear to remove the current filters from the range, then apply the conditions again one at a time.

Frequently Asked Questions

How do I add filter arrows to a heading row in Excel?
Click a cell in the data and select Data > Sort & Filter > Filter on Windows. The arrows appear in the detected heading row. If Excel selects the wrong range, select the full list, including its headings, before turning the filter on.

Can I filter without converting my data into a table?
Yes. Select a cell in a regular data range and use Data > Filter. Converting a range to a table is optional; tables are useful when you want built-in filter arrows and a range that can expand as you add records.

Why did Excel hide rows when I applied a filter?
Rows that do not match the selected values or conditions are hidden temporarily. They remain in the worksheet. Clear the filter to show them again; do not delete rows unless you intend to remove that data permanently.

Can I filter by more than one column?
Yes. Choose a condition from one column’s menu, then choose another condition from a different column. Excel displays rows that meet the active filters together. Clear individual filters or use the Clear command on the Data tab to restore the full range.

How do I remove the filter option from the sheet?
Choose Data > Sort & Filter > Filter again, or press Ctrl+Shift+L on Windows, to toggle the arrows off. On a Mac, use Data > Filter if you do not want to use the keyboard shortcut. This removes the drop-down controls but does not delete the list or its rows.

Does Excel for the web have the Filter option?
Yes. Select a cell in the data and choose Data > Filter on the web ribbon. The available menus and layout can differ slightly from desktop Excel, but the basic process of choosing values or conditions from a column arrow is similar.

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.