How to Make a Filter in Excel: Step-by-Step

Formulas & functions · Updated November 20, 2026

To make a filter in Excel for Microsoft 365 on Windows, click a cell in your data, then choose Data > Filter. Excel adds a drop-down arrow to each column heading; use an arrow to show only rows that match your choices. Filtering hides rows temporarily—it does not delete or change the underlying data.

Filters are useful for finding particular names, dates, values, or categories in a long list. Before you start, make sure the data has a clear header row and is arranged in columns, with one record per row. The steps below cover basic filters, text and number conditions, clearing a filter, and common problems.

What You’ll Need

  • An Excel workbook containing the list you want to filter.
  • A single header row with a distinct label for each column, such as Date, Region, or Status.
  • A rectangular block of data without completely blank rows or columns breaking it into separate sections.
  • Microsoft Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web have the same basic Filter command, though some ribbon details or keyboard shortcuts can differ.

Step-by-Step

  1. Check and prepare the data. Put each type of information in its own column and each item or transaction on its own row. For example, a sales list might have Order Date, Customer, Region, and Amount as headings. Use one row for the headings, avoid merged cells in the list, and remove completely blank rows or columns within the data. Make sure every heading is different so you can tell the fields apart in the filter menus. If the first row contains ordinary data rather than headings, Excel may treat it as a heading, so add a proper header row first.
  2. Select a cell in the list. Click any cell within the block of data. Excel usually detects the connected data range automatically, so selecting every cell first is not necessary. If the sheet contains several separate lists close together, select just the intended range, including its header row, before turning on Filter. Check the range’s edges before proceeding so you do not accidentally include a title, notes, or an unrelated set of data.
  3. Turn on Filter. On the Windows ribbon, choose Data > Filter. You can also use Home > Sort & Filter > Filter. The Windows keyboard shortcut is Ctrl+Shift+L. Small drop-down arrows should appear at the right side of the header cells. On a Mac, choose Data > Filter; menu placement and shortcut behavior can vary by Excel version and keyboard settings, so the ribbon command is the dependable option. Excel for the web also provides Filter on the Data tab.
  4. Open a column’s filter menu. Click the arrow in the heading for the field you want to narrow down. The menu normally shows a search box, a list of values with checkboxes, and commands for sorting or filtering. The available commands depend on the kind of values in that column. For example, Excel offers text filters for text, number filters for numbers, and date filters for dates. If the menu shows unexpected choices, check that the column contains consistent data rather than a mixture of text and numbers.
  5. Choose the values to display. To show only selected categories, clear (Select All), tick the values you want, and select OK. For example, in a Region column you could select West and Central to hide all other regions. Use the search box to find a value in a long checklist, then select the matching result. The checkbox list reflects values currently present in the filtered range; it is not a separate list of every value that could ever be entered. A funnel symbol on the header indicates that a filter is active for that column.
  6. Apply a condition when selecting values is not enough. Open the arrow and choose the appropriate option, such as Text Filters > Contains, Number Filters > Greater Than, or a date condition. Enter the value or boundary in the dialog and confirm it. For example, a number filter can show amounts above a threshold, while a text filter can find entries containing a particular word. Review the condition before applying it, especially whether it says Contains, Begins With, Equals, or a different rule; those options produce different results.
  7. Filter more than one column if needed. Apply a selection or condition in one column, then open another column’s arrow and apply a second one. Excel keeps only rows that meet the active criteria across the columns. For instance, filtering Region to West and Status to Open shows open items in the West region, not every open item plus every item in the West. The filters work together, so if a result appears missing, check the criteria in all filtered columns.
  8. Clear a filter or turn filtering off. To show all values in one column again, open that column’s menu and choose Clear Filter From followed by the column name. To clear filters across the list, select Data > Sort & Filter > Clear. This keeps the filter arrows in place but removes the active criteria. To remove the arrows as well, choose Data > Filter again. Clearing filters does not restore a previous sort order; sorting rearranges rows and is separate from filtering.

Common Mistakes

Leaving a blank row inside the list. Excel may interpret a completely empty row as the end of the data, causing some records below it to be left out. Remove the break or select the exact intended range before choosing Filter. A blank cell in an occasional field is different from a whole blank row, but inconsistent layouts can still make the range harder to identify.

Using the first data record as a heading. If the list has no header row, Excel can give the first record the filter arrows and treat its values as field names. Add descriptive headings above the records, then enable Filter again. Also avoid duplicate or empty headings; clear, unique labels make both the drop-downs and later analysis easier to follow.

Assuming hidden rows were deleted. A filter hides rows that do not meet the current criteria. They remain in the worksheet and reappear when you clear the filter. This matters if you copy or print a filtered list: check whether you intend to work with only the visible results or with the full data set before taking the next action.

Expecting a filter to find values that are not in the selected range. If you add new records below a regular filtered range, Excel may not include them automatically, particularly when a blank row separates them. Make sure new rows are inside the filtered range or use an Excel table for a list that will grow. Also check that an active filter in another column is not hiding the record you expect to see.

Mixing data types in one column. Dates stored as text and numbers stored as text can lead to confusing filter menus or comparisons. Keep each column consistent. If some entries sort or filter oddly, inspect their cell formats and contents rather than assuming the filter is broken.

Tips

  • Convert a growing list to a table. Click inside the data and press Ctrl+T on Windows, confirm the range, and leave My table has headers selected if the first row contains headings. Tables include filter arrows by default and usually extend formatting and formulas when you add a row directly beneath them. On a Mac, use Insert > Table if the shortcut differs in your setup.
  • Use the search box for long lists. Typing part of a value in the column menu can be faster than scrolling through many checkboxes. Check the selected values before you confirm; a search helps locate choices but does not itself apply a filter until you make the selection and choose OK.
  • Remember that different columns combine. Filtering a city and a status at the same time normally means “this city AND this status.” If you need alternative conditions in a single column, use the appropriate Text, Number, or Date Filters options, or select multiple listed values where suitable.
  • Keep a clean source list. One header row, consistent formats, and no merged cells make filtering more predictable. For shared or important workbooks, consider saving a copy before reorganizing the source data, especially if you are also sorting, deleting, or editing rows.
  • Distinguish sorting from filtering. Sorting changes the order of records; filtering temporarily limits which records are visible. You can use both, but clearing a filter will not necessarily undo a sort. If the original order matters, add an ID or sequence column before sorting so the records can be put back in that order.

Troubleshooting

No arrows appear: Click inside the data and choose Data > Filter again. If only part of the intended data is selected, turn Filter off, select the full range including its headings, and turn it back on. Check that the sheet is not protected in a way that prevents filtering; on a protected sheet, the workbook’s protection settings may need to allow AutoFilter use or be changed by its owner.

Some rows are missing from the filter results: Open every filtered column and review its selection. A filter in a different field can exclude the row even if the current column appears correct. Then check the data range for blank breaks and confirm that the row is actually included. For a growing list, convert it to a table or extend the range to include new entries.

The filter menu shows odd or incomplete choices: Look for inconsistent values, leading or trailing spaces, cells that look blank but contain spaces, and numbers or dates stored as text. Correct the source values, then reopen the menu. If the list has changed since the filter was applied, clearing the filter and applying it again can make it easier to review the current choices.

A text or date filter gives unexpected results: Verify the condition and the cell contents. “Contains” is not the same as “Equals,” and a date stored as plain text may not behave like an Excel date. Check a few source cells directly, standardize the column, and retry with a condition suited to the data type.

Filter is unavailable or the list behaves strangely: Confirm that you are editing the correct worksheet and that the data is not part of a protected or shared setup that restricts changes. If the worksheet has multiple separate lists, apply the filter to one range at a time rather than expecting one set of arrows to control unrelated blocks.

Frequently Asked Questions

Does filtering delete rows in Excel?
No. A filter hides rows that do not match the criteria. Clear the filter to display them again. Deleting rows is a separate action and should not be confused with hiding them.

Can I filter two or more columns at once?
Yes. Apply a filter in one column, then apply another in a different column. Excel shows records that satisfy the active conditions in all those columns. To see the effect of one condition alone, clear the other column’s filter.

Can I filter by color?
Yes, when the cells have a fill color or font color that Excel can filter. Open the column’s filter arrow, choose Filter by Color, and select the color option shown. If the option is missing, check whether the selected column contains cells with applicable colors.

How do I show all rows again?
For one field, open its arrow and choose Clear Filter From that field. To clear all active criteria, use Data > Sort & Filter > Clear. The arrows remain until you toggle Data > Filter off.

Will new rows automatically be included?
Rows added directly beside a filtered range may be detected in some cases, but do not rely on that for a list that regularly grows. Converting the range to an Excel table is a more dependable way to extend the list and its filter controls as you add records.

Can I filter without a header row?
It is better to add one first. Excel uses the top row of the selected range for field names, so without meaningful headings it can treat a data record as a header. Descriptive headings also make each filter menu easier to identify.

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.