How to Create Filters in Excel: A Complete Guide

Filtering data in Excel is one of the most practical skills you can develop for working with spreadsheets. Whether you're sorting through hundreds of sales records, tracking inventory, or analyzing survey responses, filters let you zero in on exactly what matters — without deleting or rearranging anything permanently.

What Is a Filter in Excel?

A filter in Excel temporarily hides rows that don't match your chosen criteria, leaving only the relevant data visible. The hidden rows still exist — they're just out of view until you clear or adjust the filter.

This is different from sorting, which rearranges your data. Filtering is non-destructive: your original dataset stays intact.

Excel offers two main types of filters:

• AutoFilter — the standard, built-in dropdown filter most users interact with
• Advanced Filter — a more powerful option for complex, multi-condition filtering using a separate criteria range

How to Apply an AutoFilter 🔽

AutoFilter is the quickest way to start filtering in Excel and works across most versions, including Excel 2016, 2019, 2021, and Microsoft 365.

Step-by-step:

1. Click any cell inside your dataset (Excel will detect the range automatically)
2. Go to the Data tab on the ribbon
3. Click the Filter button in the Sort & Filter group
4. Small dropdown arrows will appear in your header row
5. Click the arrow on any column you want to filter
6. Use the checkboxes or search box to select which values to show
7. Click OK to apply

You can filter by multiple columns simultaneously. Each additional filter narrows the results further — they work together using AND logic (rows must meet all active filter conditions to appear).

Keyboard shortcut:Ctrl + Shift + L toggles AutoFilter on and off.

Filtering by Specific Criteria

Clicking the dropdown arrow gives you several filtering options beyond simple checkboxes:

For example, to show only rows where a sales figure exceeds 5,000, you'd click the column's dropdown arrow, hover over Number Filters, then select Greater Than, and enter your value.

How to Use the Search Box in a Filter Dropdown

In Excel 2010 and later, each filter dropdown includes a search box at the top. This is especially useful when a column contains dozens or hundreds of unique values.

Type a partial word or number to instantly narrow the list of checkboxes — then select what you need. This saves time compared to scrolling through a long list manually.

Filtering with Multiple Conditions in One Column

When you need more flexibility within a single column — like showing rows where a value is between two numbers or contains either one term or another — use the Custom AutoFilter:

1. Click the column's filter dropdown
2. Select Text Filters or Number Filters
3. Choose Custom Filter...
4. Set your first condition, then choose And or Or
5. Set your second condition and click OK

And means both conditions must be true. Or means either condition qualifies a row.

How to Use Advanced Filter

Advanced Filter is designed for situations where AutoFilter reaches its limits — particularly when you need to filter based on complex criteria across multiple columns, or when you want to copy filtered results to a different location.

To use Advanced Filter:

1. Set up a criteria range — a separate area on your sheet with the same column headers and your conditions listed below them
2. Click anywhere in your main dataset
3. Go to Data → Advanced (in the Sort & Filter group)
4. Choose whether to filter in place or copy results to another location
5. Define your List Range (the dataset) and Criteria Range (your conditions block)
6. Click OK

Conditions on the same row act as AND logic. Conditions on different rows act as OR logic. This gives you precise control over complex scenarios. 🎯

Clearing and Removing Filters

To clear a filter from a specific column, click its dropdown arrow and select Clear Filter From [Column Name].

To remove all filters at once, go to Data → Clear (in the Sort & Filter group), or press Ctrl + Shift + L to toggle filters off entirely.

Note that clearing a filter and removing filters are different: clearing shows all rows again while keeping the filter dropdowns active; removing filters takes the dropdowns away entirely.

Variables That Affect How You Filter

How filtering behaves — and which approach works best — depends on several factors specific to your situation:

• Dataset size: Very large datasets (tens of thousands of rows) may benefit from Power Query or table-structured filtering rather than basic AutoFilter
• Data structure: Filters work best when your data has a clear header row and no blank rows within the dataset
• Excel version: Some filter behaviors, like dynamic array spilling with FILTER function, are only available in Microsoft 365 and Excel 2019+
• Use case: One-time analysis calls for different filtering than ongoing dashboards or reports that refresh regularly
• Skill level: The FILTER function (available in newer versions) lets you extract filtered results dynamically into a separate range using a formula — powerful, but requires comfort with Excel functions

The FILTER function specifically (=FILTER(array, include, [if_empty])) returns filtered results as a live, spillable range — meaning the output updates automatically as source data changes. This is meaningfully different from AutoFilter, which is a manual, visual tool.

What makes the right filtering approach depends heavily on whether you're doing a quick one-off lookup, building a repeatable workflow, or constructing something that others will use and maintain. Your dataset's structure, the version of Excel you're running, and how often the filtering needs to be repeated all point toward different solutions.