How to Filter Data in Excel
Excel offers three ways to filter data: AutoFilter (the standard dropdowns), Advanced Filter (criteria ranges), and the FILTER function (live formula-based, Excel 365). Each fits a different situation.
⚡ Quick Answer
Click any cell in your data, then Data → Filter. Dropdown arrows appear on each column header. Click an arrow, tick or untick values, or use text/number/date filters. Non-matching rows hide (not delete).
Method 1: AutoFilter (most common)
Click inside your data
Any cell in the dataset. Excel expands the filter automatically.
Click Data → Filter
Dropdown arrows appear on each column header.
Click a filter arrow
A menu opens with all unique values in that column.
Choose your filter
Tick/untick specific values, or use Text Filters (contains, begins with, ends with) or Number Filters (greater than, between, top 10) or Date Filters (this week, last month, etc.).
Click OK
Rows not matching are hidden. Row numbers turn blue and the filter arrow shows a funnel icon.
Every filter dropdown has a search box at the top. Type to narrow the list — much faster than scrolling through hundreds of unique values. You can also use wildcards: *inc matches values ending in "inc".
Method 2: FILTER function (Excel 365)
Creates a live-filtered copy that updates when source data changes.
=FILTER(A2:C100, B2:B100="Approved")Returns all rows from A2:C100 where column B equals "Approved". Result spills into adjacent cells.
Multiple conditions with FILTER
=FILTER(A2:C100, (B2:B100="Approved") * (C2:C100 > 1000))
=FILTER(A2:C100, (B2:B100="Approved") + (B2:B100="Pending"))* is AND. + is OR. Enclose each condition in parentheses.
FILTER with a fallback for empty results
=FILTER(A2:C100, B2:B100="X", "No matches")Third argument is what shows when nothing matches, instead of an error.
Method 3: Advanced Filter (for complex criteria)
When AutoFilter isn't enough — many conditions across multiple columns with OR logic.
Set up a criteria range
In empty cells above or beside your data, create a small table with the same column headers as your dataset. Below each header, enter the criteria values. Values in the same row = AND; values in different rows = OR.
Open Data → Advanced (Sort & Filter group)
The Advanced Filter dialog opens.
Set list range and criteria range
List range = your dataset. Criteria range = the mini criteria table you built.
Choose "Filter in place" or "Copy to another location"
In place: hides non-matching rows. Copy: puts matching rows in a new location without affecting the source.
Common filter tasks
Filter by color
Click any filter arrow → Filter by Color → pick the cell color or font color. Filters rows where cells in that column have the selected color.
Filter unique values
Advanced Filter has a "Unique records only" checkbox that returns distinct rows. Or use =UNIQUE(A2:A100) in Excel 365.
Filter top 10
On a number column, click filter arrow → Number Filters → Top 10. Choose top or bottom, count or percent. Great for "top 10 customers by revenue" summaries.
How to clear filters
- Clear one column's filter: Click that column's filter arrow → Clear Filter From "[column]".
- Clear all filters: Data → Clear (in the Sort & Filter group).
- Turn off filtering entirely: Data → Filter (toggle button — same as turning it on).
Keyboard shortcuts
Filter Shortcuts
Excel Wizard — filter with plain English
AutoFilter is fast for simple conditions but painful for "show me all Q3 orders over $10K from customers in California that haven't shipped yet." Excel Wizard's AI lets you describe the filter in plain English — it builds and applies the right criteria automatically. No dropdown-clicking chain required.
Install Excel Wizard →Frequently asked questions
How do I filter data in Excel?
Click any cell in your data, go to Data tab, click Filter. Dropdown arrows appear on each column header. Click an arrow to see filter options: check/uncheck values, use text or number filters, or search for specific values. Rows that don't match are hidden (not deleted).
How do I clear a filter in Excel?
To clear one column's filter: click its filter arrow and choose Clear Filter From. To clear all filters at once: Data → Clear. To turn off AutoFilter entirely: Data → Filter (toggle). Filters hide rows temporarily — clearing shows them again without changing your data.
What's the difference between Filter and Sort?
Filter hides rows that don't match your criteria — data stays in place, rows just aren't displayed. Sort rearranges rows in a different order — nothing is hidden but the sequence changes. Filter answers 'which rows match?' Sort answers 'what order?'. You can filter and sort together — filter to a subset, then sort the visible rows.
How does the FILTER function work in Excel?
FILTER (Excel 365) returns a spilled array of rows matching a condition. Syntax: =FILTER(array, include, [if_empty]). Example: =FILTER(A2:C100, B2:B100="Approved") returns all rows where column B equals 'Approved'. Unlike AutoFilter which hides rows in place, FILTER creates a live-updating filtered copy in a new location.