How to Apply a Filter in Excel to Find Data Fast

Formulas & functions · Updated November 20, 2026

To apply a filter in Excel, click a cell in your data, then choose Data > Filter. Use the dropdown arrow in a column heading to choose which rows to show. Filtering hides rows that do not match your criteria; it does not delete or change the underlying data. These steps are for Excel for Microsoft 365 on Windows, and the same ribbon commands are generally available in Excel for Mac and Excel for the web.

What You’ll Need

  • A worksheet with a block of related data, such as a list of orders, contacts, or inventory.
  • A clear heading in the top row of each column. For example, use Date, Customer, Region, and Amount rather than leaving headings blank.
  • Permission to edit the worksheet if it is protected. You can usually view data on a protected sheet, but filtering may be unavailable unless the sheet allows it.

You do not need to sort the data first or create a formula. If the list is already an Excel table, its header row should already have filter arrows, so you can skip turning on the filter and go straight to choosing criteria.

Step-by-Step

  1. Check the data layout. Make sure the list has one row of headings and consistent columns beneath it. Each row should represent one record, such as one order or one person. Avoid putting a separate title, subtotal, or blank row inside the list: Excel may treat it as a boundary and leave some records outside the filter range. If there are blank rows or columns around the list, select the entire data block yourself before applying the filter.
  2. Click inside the list. If your data is one continuous block with headings, click any cell within it. Excel usually detects the connected range automatically. If it selects the wrong range, drag to select the full block, including the headings and all data rows. Do not select only the column you want to filter; Excel needs the full records so it can hide complete rows while keeping each field matched to the right record.
  3. Turn on Filter. On the Windows ribbon, choose Data > Filter. You can also use Home > Sort & Filter > Filter. The keyboard shortcut on Windows is Ctrl+Shift+L; press it again to switch the filter arrows off. In Excel for Mac, use Data > Filter if the shortcut differs on your keyboard or in your version. In Excel for the web, use the Data tab’s Filter command. Small dropdown arrows should appear in the selected headings.
  4. Open the heading menu you need. Click the arrow in the column you want to narrow down. The menu contains a search box and a checklist of distinct values, along with options for sorting and filtering. The choices depend on the column’s contents: a text field offers text filters, numeric data offers number filters, and dates offer date filters. Filtering one column does not remove the other headings or rearrange the remaining records.
  5. Choose specific values when you know them. In the checklist, clear (Select All), then check only the entries you want displayed. For example, in a Region column, select West and Central to show records from those regions. Use the search box to find a value in a long checklist; check the matching result, and confirm that the other values are not still selected if you want only that match. Click OK to apply the selection. The visible rows now meet that column’s selection.
  6. Use a condition when you need a range or pattern. Open the column menu and choose the relevant filter option, such as Text Filters, Number Filters, or Date Filters. For example, choose Number Filters > Greater Than, enter 100, and confirm to show values above 100. For text, you can use options such as Contains to find entries with a word or fragment. For dates, choose a period or a custom date condition. The exact menu options depend on the data type Excel recognizes.
  7. Add another column filter if needed. Apply a criterion in a second heading to narrow the already-filtered results further. For example, filter Region to West and then filter Status to Open. Excel displays records that meet both column filters. A row must pass every active column’s criteria to remain visible. To broaden the results, change or clear a criterion rather than assuming that a second column’s choices work as alternatives to the first.
  8. Review and clear the results. Check the visible rows and the heading arrows to see which columns have filters. A funnel symbol on an arrow indicates that a filter is active. To remove a filter from one column, open its menu and choose Clear Filter From that column. To clear the current worksheet’s filter criteria, choose Data > Clear. To remove the arrows entirely, choose Data > Filter again. Clearing a filter reveals hidden rows; it does not restore deleted data because filtering does not delete rows.

If you regularly work with this list, you can convert it to a table using Insert > Table or the Windows shortcut Ctrl+T. Confirm that My table has headers is selected. A table adds filter controls to its headings and generally extends to include new rows typed directly beneath it, which helps keep an expanding list organized. If the range is already a table, do not create a second table just to filter it.

Common Mistakes

  • Selecting only one column: This can produce a filter range that does not include the complete records. Start by clicking inside a continuous list or explicitly select the entire block, including its headings.
  • Using blank or duplicate headings: Blank headings make the fields hard to identify, and duplicate names can make menus confusing. Give every column a distinct, descriptive heading before filtering.
  • Expecting filters to combine as alternatives: Filters on separate columns are combined, so a row must meet both conditions. If you filter Region to West and Status to Open, you see West records that are Open, not every West record plus every Open record.
  • Thinking rows disappeared permanently: Filtered-out rows are hidden, not erased. A filter can make row numbers skip, which is normal. Clear the criteria to show the full list again before deciding that information is missing.
  • Filtering inconsistent data as if it were one type: Dates stored as text or numbers mixed with words may not offer the expected date or number options. Standardize the column’s values and formats if the menu behaves unexpectedly.

Tips

  • Use search for long lists. If a menu contains hundreds of customer names, type a distinctive part of a name in its search box instead of scrolling through every entry. Check the selected values before applying the filter, especially if you previously filtered that column.
  • Keep one field per column. A column that mixes city and state, or combines a date with a note, is harder to filter precisely. Separate information into consistent fields so you can filter each aspect independently.
  • Use conditional filters for comparisons. A checklist is convenient for choosing known entries, while conditions such as greater than, begins with, or between are more useful for values that form a range or share a pattern.
  • Check for active filters before sharing or printing. A worksheet can look incomplete simply because some rows are hidden by criteria. Clear filters or tell the recipient which filters are active so they understand why only part of the list appears.
  • Do not confuse filtering with sorting. Sorting changes the order of the visible records. Filtering limits which records are shown. You can use both, but check which command you chose if rows appear in a different order.

Troubleshooting

No filter arrows appear: Click inside the data and choose Data > Filter. If arrows still do not appear, check whether the range includes a clear heading row and whether the worksheet is protected. A protected sheet may block filtering unless its protection settings allow users to use AutoFilter. If you selected a range that does not include the headings, remove the filter, select the correct block, and apply it again.

Some records are not included: Look for a completely blank row or column dividing the list, or data that starts outside the selected range. Clear the filter, select the entire intended block, then turn Filter on again. If the list is an Excel table, check whether the missing records are actually inside the table; typing or pasting far below it may not extend the table automatically.

A value you expect is missing from the menu: Clear filters in other columns first, because other active criteria may hide the row containing that value. Then check spelling, extra spaces, and whether the value is in the same column as expected. If the menu has a search box, remove any text in it. A value may also be treated differently because numbers or dates are stored as text, or because the column contains mixed types.

The date or number options seem wrong: Excel may not recognize the entries as dates or numbers. Check a few cells, correct inconsistent values, and make sure the entries are stored in a consistent type. Changing how a cell looks with a number format does not always convert text into a real date or number. Reapply the filter after correcting the column.

The filter seems stuck or results are confusing: Check the arrows for funnel symbols and clear criteria one column at a time, or use Data > Clear to remove the active filters. If a table or worksheet is shared, another user’s current view or filters may affect what you see. Confirm the range and criteria before editing or deleting any rows; filtering itself should not remove data.

Frequently Asked Questions

How do I filter by more than one value in a column?

Open that column’s dropdown, clear (Select All), check each value you want to keep, and click OK. You can also use a condition such as Contains or Greater Than when selecting individual values would be inconvenient.

Can I filter two columns at the same time?

Yes. Set a criterion in one column, then set another in a different column. Excel shows rows that satisfy both active filters. To show alternatives within one column, select multiple values in that column’s checklist.

How do I show all rows again?

Open an active column menu and choose Clear Filter From that column, or use Data > Clear to clear the current filter criteria. The hidden rows will reappear, while the filter arrows can remain available for another search.

Does applying a filter delete rows?

No. A filter hides rows that do not match the criteria; it does not delete or edit them. Clear the filter to display the full list. Be careful not to select and delete visible rows if your intention is only to hide them temporarily.

Can I filter a range that has blank cells?

Yes, but blank rows inside the data can cause Excel to detect only part of the list. A blank cell within a field can also appear as (Blanks) in that column’s menu. Remove unintended separator rows or explicitly select the full range, and use the blank option when you want to find records with empty fields.

Why can’t I filter a protected worksheet?

Worksheet protection can prevent filtering unless the owner allowed AutoFilter use when protecting the sheet. Ask the owner to adjust the protection settings or provide an unprotected copy. Do not remove protection if you are not authorized to change the workbook.

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.