How to Filter Data in Google Sheets
Google Sheets has three filtering approaches: Create a filter (affects everyone), Filter views (personal filters that don't disturb collaborators), and the FILTER function (formula-based, live). For shared sheets, filter views are the standard choice.
⚡ Quick Answer
Click any cell in your data, then Data → Create a filter (or Ctrl + Shift + L). Filter arrows appear on column headers. Click an arrow to filter by value, condition, or color.
Method 1: Create a filter (most common)
Standard filtering. Note: on shared sheets, filters affect what other viewers see too.
Click inside your data
Any cell in the dataset.
Click Data → Create a filter
Or press Ctrl + Shift + L. Filter arrows appear on each column header.
Click a filter arrow
Menu opens. Sections: Sort options at top, Filter by color, Filter by condition, Filter by values.
Choose your filter
Tick/untick specific values, or use a condition (Text contains, Number is greater than, Date is before, etc.). Click OK.
Rows non-matching hide
Row numbers turn blue and the filter arrow shows a funnel icon.
On shared sheets, applying a filter changes what other collaborators see. If they're actively looking at the sheet, their view suddenly hides rows. Use Filter Views (Method 2) instead for personal filtering on shared docs.
Method 2: Filter views (collaboration-friendly)
Personal filters that don't affect other viewers. Save multiple and switch between them.
Click Data → Create filter view
Or from an active filter, Data → Filter views → Save as filter view.
Configure filters
Sheet turns dark gray at the border. Filter arrows appear. Set your filters and sorts.
Name the filter view
Top-left of the sheet, click the default name and rename it (e.g., "My open tasks").
Close when done
Click X in the top-right corner. Your filter view is saved.
Return later
Data → Filter views → [Your saved view]. Others don't see this filter unless they explicitly open the same view.
Any collaborator can create their own filter views without disturbing others. Great for personal dashboards ("My open items", "This week's due", "High priority only") that live inside a shared workbook without breaking anyone else's view.
Method 3: FILTER function (live)
Creates a filtered copy in a new location that updates as source 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
=IFERROR(FILTER(A2:C100, B2:B100="X"), "No matches")
Wrap in IFERROR to show a message when nothing matches, instead of an error.
Common filter tasks
Filter by color
Click a filter arrow → Filter by color → pick fill color or text color. Rows with matching color show; others hide.
Filter by condition
Filter arrow → Filter by condition. Options include: is empty, is not empty, text contains, text does not contain, greater than, between, custom formula. Custom formula is powerful — e.g., =A1>AVERAGE(A:A) filters to above-average rows.
Filter by search
Filter arrow → search box at top. Type to narrow the values list — much faster than scrolling.
How to clear filters
- Clear one column's filter: Click that column's filter arrow → Clear at the top of the filter panel.
- Turn off filtering entirely: Data → Remove filter.
- Close a filter view: Click the X in the top-right corner of the filter view banner.
Keyboard shortcuts
Filter Shortcuts
Sheets Wizard — filter with plain English
Filter menus are fast for simple conditions but painful for "show me all Q3 orders over $10K from customers in California that haven't shipped yet." Sheets 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 Sheets Wizard →Frequently asked questions
How do I filter data in Google Sheets?
Click any cell in your data. Click Data → Create a filter (Ctrl + Shift + L). Filter arrows appear on each column header. Click an arrow to filter by values, condition, or color. Rows that don't match are hidden temporarily — they're not deleted.
What's the difference between Filter and Filter view in Google Sheets?
A regular Filter affects everyone viewing the sheet — filtering rows hides them for all collaborators. A Filter view is personal — you filter and sort the data for yourself without affecting others. Multiple filter views can be saved and switched between. Filter views are the collaboration-friendly choice.
How does the FILTER function work in Google Sheets?
FILTER returns a spilled array of rows matching a condition. Syntax: =FILTER(range, condition1, [condition2], ...). Example: =FILTER(A2:C100, B2:B100="Approved") returns all rows where column B equals 'Approved'. Unlike the Filter menu which hides rows in place, FILTER creates a live-updating filtered copy in a new location.
How do I clear all filters in Google Sheets?
To turn off filtering entirely: Data → Remove filter. To clear one column's filter but keep filtering on: click the filter arrow → click 'Clear' at the top of the filter panel. To close a filter view: click the X in the top-right corner of the filter view banner.