How to Put a Filter in Excel to Find Data More Easily

Formulas & functions · Updated November 20, 2026

To put a filter in an Excel sheet, click a cell in your data and select Data > Filter. Excel adds a dropdown to each column heading; use a dropdown to show only rows that match criteria you choose. Filtering hides nonmatching rows without deleting them, making a long list easier to review, compare, or print.

These instructions are for Excel for Microsoft 365 on Windows. Before you start, check that your data has one clear header row and that there are no completely blank rows or columns splitting the list. You can filter text, numbers, dates, and cells with or without values. The same basic Data tab command is available in Excel for Mac and Excel for the web, though ribbon layouts and keyboard shortcuts can differ.

What You’ll Need

  • An Excel workbook containing a list or table of data.
  • A header row with a distinct label for each column, such as Date, Region, or Status.
  • Optional: an idea of which value or condition you want to find, such as one department, dates after a certain day, or amounts above a threshold.

Step-by-Step

  1. Check and prepare the data. Make sure each column has a heading in the first row of the list, and that the rows below contain records. Avoid merged cells in the range, repeated header rows within the list, and completely blank rows or columns in its middle. A blank line above the headings is fine if you select a cell inside the list before applying the filter, but a continuous block is easier for Excel to recognize. If there are notes or totals beside the list, keep them outside the data range so they are not treated as records.
  2. Select a cell in the data. You usually do not need to highlight the whole range. Click any populated cell in the list, preferably a cell below the header. Excel will detect the connected block of data and include its header row. If the sheet has nearby content that Excel might mistake for part of the list, select the exact range instead, including the column headings and all the records you want to filter.
  3. Turn on the filter controls. On the ribbon, choose Data > Filter. Small dropdown arrows should appear beside the headings in the selected list. On Windows, you can also press Ctrl+Shift+L to toggle filters on or off. On a Mac, use Data > Filter if the shortcut differs for your keyboard or Excel version. In Excel for the web, select a cell in the range and use Data > Filter on the ribbon.
  4. Open the dropdown for the column you want to filter. Select the arrow beside a heading, not a cell in the body of the table. The menu contains a search box, a list of values, and filter options suited to that column’s data type. For a simple selection, clear Select All and tick only the value or values you want to keep visible. For example, in a Region column, select West to display only records labeled West. Choose OK to apply the selection.
  5. Use a condition when a simple value selection is not enough. For text, open the column dropdown and choose Text Filters, then select a condition such as Contains, Begins With, or Equals. Enter the text and confirm. For a number column, choose Number Filters and a condition such as Greater Than or Between. For dates, use Date Filters to choose a date range or a period such as the current month, where available. The exact options depend on the selected column’s values.
  6. Combine filters across columns if needed. Apply a filter to one column, then open another column’s dropdown and choose its criteria too. Excel displays only rows that meet all the active column filters. For example, filtering Region to West and Status to Open shows West records whose status is Open—not every West record plus every open record. The dropdown arrows change to indicate that filters are active.
  7. Review the visible results. Look at the row numbers: gaps indicate that some rows are hidden by the filter. Check the column dropdowns to see which criteria are active, and confirm that the visible rows have the values you intended. Filtering does not rearrange the remaining records by itself; if you also want to order the results, open a column dropdown and choose a sort option, or use the commands in Data > Sort.
  8. Clear a filter or remove the controls. To show all values again in one column, open that column’s dropdown and choose Clear Filter From followed by the column name. To clear all active filters while leaving dropdowns in place, choose Data > Clear. To remove the dropdowns entirely, choose Data > Filter again or use Ctrl+Shift+L on Windows. Clearing criteria and turning off the filter are different actions; use the first when you expect to filter again.

For a list you update often, you can convert the range into an Excel table with Insert > Table and confirm that My table has headers is selected. Tables include filter arrows in the header row by default and usually expand to include new adjacent rows. This is optional: the Filter command works on a regular range, too. If the workbook already contains a table, its header dropdowns may already be available.

Common Mistakes

  • Leaving a blank row inside the list. Excel may detect only part of the data, so some records do not get filter arrows or do not respond to filtering. Remove the separator row or select the full intended range before turning on the filter.
  • Using duplicate or missing headings. Give every column a heading, and make each heading distinct. Blank or repeated labels make it harder to tell which dropdown belongs to which field and can cause problems when the range is turned into a table.
  • Mixing data types in one column. A column with dates stored as text alongside real dates, or numbers alongside text entries, may show unexpected filter categories. Keep each column consistent and correct entries that are stored in the wrong format.
  • Assuming hidden rows were deleted. A filter hides rows that do not meet the criteria; it does not erase them. Clear the filter to reveal them. Before copying or deleting records, check what is visible and which rows are selected so you do not act on the wrong portion of the list.
  • Expecting filters on a selected cell only. The arrows apply to the detected range, not just the column or cell you clicked. Check the boundaries of that range, particularly if totals, notes, or another list are close by.

Tips

  • Use the search box for long value lists. Open a dropdown and type part of a value into its search box. Select the matching result or results, then apply the filter. This is quicker than scrolling through hundreds of names or product codes.
  • Use filters to narrow first, then sort. Filtering answers which rows should be visible; sorting changes their order. You can use both together, but applying one does not automatically do the other.
  • Choose column labels that describe the contents. Headings such as Order Date and Payment Status are easier to identify than generic names such as Column 1. Clear labels also help colleagues understand what the filter is showing.
  • Keep a clean source list. Store one record per row and one field per column. If a cell contains several categories separated by commas, Excel treats the whole cell as one value in the filter list; split the information into separate columns if you need to filter each category independently.
  • Check whether you are in a table. Clicking inside a table shows the Table Design tab in Excel for Windows. Tables are useful for growing lists, but you do not need to convert a range just to apply a basic filter.
  • Save before major cleanup. Filters are reversible, but deleting visible records or changing values is not the same as filtering. Save a copy before making broad edits, especially when you are not certain which rows are hidden.

Troubleshooting

No dropdown arrows appear: Confirm that a cell in the data range is selected and use Data > Filter. If the arrows still do not appear, check whether the worksheet is protected, whether the selected range includes the heading row, and whether another list or blank row is confusing the detected boundaries. You can select the complete range and try the command again. If the data is already an Excel table, look for arrows in its header row rather than adding another filter.

Some records do not change when you filter: The rows may be outside the filtered range, or a blank row may split the list. Clear the filter, select the full range including its headers and records, and apply Filter again. Also check for hidden rows or columns that make the range boundary difficult to see.

A value seems to be missing from the dropdown: Clear the current filter and check the source cells for extra spaces, inconsistent spelling, or values stored in different formats. For instance, 0012 stored as text may be treated differently from the number 12. The dropdown list reflects the values in the filtered range, so a value outside that range will not appear.

Date or number options look wrong: Check that the entries are actual dates or numbers rather than text that only looks like one. Select a questionable cell and inspect its value or number format. Correct inconsistent entries, then reopen the dropdown. If Excel has grouped dates into years or months, expand the relevant group or use the available date filter conditions.

You cannot edit or filter the sheet: A protected worksheet can restrict filtering, depending on how protection was set up. Ask the workbook owner to allow filtering or unprotect the sheet if you are authorized. In a shared workbook, another user’s edits or a saved view can also affect what you see; confirm that you are using the intended sheet and view before changing the data.

Frequently Asked Questions

Does filtering delete rows in Excel?

No. It temporarily hides rows that do not meet the chosen criteria. Clear the filter to display them again. Deleting rows is a separate action and can permanently change the workbook.

Can I filter more than one column at a time?

Yes. Apply a condition in one column, then apply another in a different column. Excel shows only rows that satisfy every active column filter. Clear an individual column’s filter when you want to broaden the results without removing the others.

Can I filter a list without turning it into a table?

Yes. Click inside a regular data range and select Data > Filter. A table is optional; it is helpful for lists that grow because its formatting and filter controls can extend with new records.

How do I show all rows again?

Open a filtered dropdown and choose Clear Filter From for that column, or use Data > Clear to clear active filters across the range. The dropdown arrows remain until you turn Filter off.

Can I filter by color or by blank cells?

Often, yes. A column dropdown can offer Filter by Color when the cells use supported fills or font colors. To find empty entries, open the dropdown and select (Blanks) if it appears. The choices depend on the data and formatting in that column.

Why does my filter show unexpected results?

Check the range boundaries, blank rows, spelling, extra spaces, and mixed data types. Also confirm whether more than one column has an active filter. Clearing all criteria and applying them again can help you identify which condition is narrowing the results.

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.