How to Insert a Filter in Excel to Sort and Find Data

Formulas & functions · Updated November 20, 2026

To insert a filter in Excel for Microsoft 365 on Windows, click a cell in your data range, then choose Data > Filter or press Ctrl+Shift+L. Excel adds a dropdown to each column heading; use those dropdowns to show only rows that match your criteria. Before you start, make sure your data has one clear header row and no fully blank rows splitting the range.

What You’ll Need

  • An Excel workbook containing a list or table, such as a contact list, inventory sheet, or sales log.
  • A single header row with a heading for each column. Use distinct, nonblank labels such as Date, Product, and Amount.
  • Permission to make changes to the worksheet. A protected sheet may prevent you from turning filters on or changing them.

You do not need to install an add-in or write a formula. Filters hide rows that do not match your choices; they do not delete or rearrange the underlying records unless you also choose a sort command.

Step-by-Step

  1. Check and organize the data. Put one field in each column and one record in each row. For example, a sales list could have Date, Salesperson, Region, and Amount across its first row, with each sale on a separate row below. Remove completely blank rows or columns from the middle of the list, since Excel may treat them as boundaries and filter only part of your data. Avoid merged cells in the header row. If you have a title above the headings, leave it outside the data range.
  2. Choose the range to filter. Click any cell inside a continuous block of data. Excel usually detects the connected list automatically, so you do not need to drag over every row. If the worksheet has nearby data that should not be included, select just the intended range, including the header row. Check the first and last columns and the last populated row before proceeding. Selecting only the headings or only one column can lead to an incomplete filter range.
  3. Turn on Filter. On the Windows ribbon, select Data > Filter, in the Sort & Filter group. The keyboard shortcut is Ctrl+Shift+L. Small dropdown arrows should appear beside the column headings. If they do not, check that you selected the data rather than a blank cell and that the worksheet is not protected. On a Mac, select a cell in the data and use Data > Filter; the menu is a dependable alternative if your keyboard settings differ. In Excel for the web, use Data > Filter as well.
  4. Open a column dropdown. Click the arrow beside the heading for the field you want to narrow. For text columns, the menu typically shows a checklist of distinct values and a search box. For dates, Excel may group available items by year, month, or day. For numeric columns, you can select values or use the Number Filters options. The menu changes according to the type of data in that column.
  5. Choose the rows to display. To include selected text values, clear Select All, tick the values you want, and click OK. For instance, in a Region column, selecting East and West hides records for other regions. To find a specific value in a long list, type it in the dropdown search box, select the matching result, and apply the filter. For a numeric condition, open Number Filters and choose an option such as Greater Than; enter the limit and confirm. For dates, use the available date-filter options or select the required dates in the list.
  6. Apply more than one condition when needed. You can filter another column without removing the first filter. For example, filter Region to East, then filter Status to Open to show only open records from the East. Excel combines filters across columns, so a row must meet the active conditions to remain visible. Look for a filter symbol on a heading to see which columns have criteria applied. If you use multiple values within one column, the checklist usually treats the selected values as alternatives; filters on separate columns narrow the results together.
  7. Review the results and make changes carefully. Check the visible row numbers and the filtered-column icons to confirm that the intended criteria are active. Edit a visible cell if needed, bearing in mind that hidden rows remain in the worksheet. To adjust one condition, reopen that column’s dropdown and change its selections. To sort the visible values, use the dropdown’s Sort A to Z or Sort Smallest to Largest command as appropriate. Sorting changes row order; filtering only controls which rows are shown.
  8. Clear the filter or remove the arrows. To show all rows again while keeping the dropdowns, open a filtered column’s dropdown and choose Clear Filter From followed by its column name. Repeat for other filtered columns, or use Data > Clear to clear the active filters for the range. To remove the dropdown arrows entirely, select a cell in the range and choose Data > Filter again, or press Ctrl+Shift+L on Windows. Clearing criteria and turning off Filter are different actions: the first restores rows but keeps the controls.

Alternative for a growing list: Convert the range to an Excel table by selecting a cell in the data and choosing Insert > Table. Confirm the range and that My table has headers is selected, then click OK. Excel tables include filter dropdowns in their header row and generally expand when you add data directly below them. If you only need filters on the current range, using Data > Filter is quicker and does not convert the range.

Common Mistakes

  • Including a title or extra notes in the selection. Excel may interpret a title as the header row, leaving you with an unexpected filter. Select the actual column headings and records, not explanatory text above or beside them.
  • Leaving a blank row in the middle of the list. A completely blank row can make Excel detect only the block above it. Remove the separator or explicitly select the full range, including rows below the gap.
  • Using blank, duplicate, or unclear headings. Blank headings can produce generic labels, and duplicate names make it harder to identify the right dropdown. Give each column a descriptive, distinct heading before filtering.
  • Thinking hidden rows have been deleted. A filter hides records from view; it does not remove them. Avoid copying or deleting visible results without checking what is selected, especially if you intend to preserve all records.
  • Expecting filters to work across unrelated blocks. A normal AutoFilter applies to one contiguous range on a worksheet. If two independent lists need separate filter controls, convert each list to its own Excel table rather than trying to filter both as one range.
  • Confusing sorting with filtering. Sorting changes the order of records, while filtering hides rows that do not match a condition. If the records appear in a new order, use the appropriate sort command or undo the sort if it was accidental.

Tips

  • Use consistent data types within each column. Dates stored as real Excel dates and amounts stored as numbers give you more useful date and number-filter options than values entered as text.
  • Keep one record per row. Avoid putting several regions, dates, or products into a single cell if you expect to filter those details separately.
  • Use the dropdown search box for a long list of names or categories instead of scrolling through every checkbox. Clear the search text if you want to see the full list again.
  • When checking whether a filter is active, look at the heading’s dropdown icon and at the row numbers. Excel commonly skips row numbers for records hidden by a filter, which helps distinguish filtered results from a short list.
  • For a reusable dataset that grows over time, an Excel table is often easier to manage than a fixed range. Add new records directly below the table so Excel can extend the table and its formatting and filter controls.
  • If you share the workbook, tell collaborators that some records may be hidden by filters. They can use the dropdowns or Data > Clear to reveal the full list before drawing conclusions from the visible rows.

Troubleshooting

No dropdown arrows appear: Click inside the data and try Data > Filter again. If the command appears unavailable, press Enter or Esc to leave cell-editing mode, check that the worksheet is not protected, and confirm that the workbook is not in a state that prevents editing. Also verify that the selection includes a header row and at least one data row. If you are using a filtered table, click inside that table rather than a separate nearby range.

Only part of the list is filtered: Look for a blank row or column dividing the records, or for data outside the range you selected. Turn off the current filter, remove unwanted gaps if appropriate, select the complete intended range, and apply Filter again. If the data has a blank line that must remain, select the full range manually rather than relying on Excel to detect the block.

A value you expect is missing from the dropdown: Clear any existing criteria first, then inspect the source cells for leading or trailing spaces, inconsistent spelling, or values stored in a different format. A text value that looks like a number may not behave like a numeric value. Also check whether the value is in a row outside the filtered range or whether another column’s filter is hiding its row.

The filter seems to show no results: Reopen the dropdown and check the selected values, search text, and number or date condition. A condition in another column may also be excluding the rows. Clear all filters with Data > Clear to restore the full list, then apply one condition at a time to locate the source of the empty result.

Filter is unavailable or will not change: Check whether the sheet is protected; the worksheet owner may need to allow filtering or unprotect the sheet. If the list is part of a shared or coauthored workbook, another view or editing restriction may affect what you can change. Save your work, check the workbook’s editing status, and ask the owner for access if necessary.

Frequently Asked Questions

Does inserting a filter change or delete my data?
No. A filter hides rows that do not match the chosen criteria. The records remain in the worksheet and reappear when you clear the filters. Sorting is a separate action that changes row order.

Can I filter more than one column at once?
Yes. Apply a condition from each column’s dropdown. Excel displays rows that meet the active conditions across those columns, which is useful for combining a category, date range, and status.

How do I filter for blank cells?
Open the relevant column’s dropdown and select (Blanks) in its checklist, if that option is shown. To show completed entries instead, select the nonblank values. Check for cells containing spaces, because a space is not the same as an empty cell.

Can I filter a range that does not have headings?
It is better to add a clear header row first. Excel uses the first row of the filtered range as headings, and meaningful labels make the dropdowns easier to identify. Without headings, the first row of actual data may be treated as the header.

Why do some filter options differ between columns?
Excel offers menus based on the contents of each column. Text columns provide text-related choices, numeric columns provide number filters, and date columns provide date-related choices. Mixed or inconsistently stored values can make a column behave differently than expected.

Can I have separate filters for two lists on one worksheet?
A standard AutoFilter is for one range at a time on a worksheet. To give two independent lists their own controls, make each list an Excel table with Insert > Table. Keep each table’s data and headings within its own range.

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.