Google Sheets Filter Multiple Columns: A Step-by-Step Guide
Learn how to filter multiple columns in Google Sheets with FILTER, QUERY, and filter views. This practical guide covers syntax, real-world examples, tips, and common pitfalls for students, professionals, and small business owners.
This guide helps you filter multiple columns in Google Sheets using three reliable methods: the FILTER function for precise multi-criteria filtering, the QUERY function for SQL-like querying, and filter views to save and share complex criteria. You’ll see practical examples, syntax, and best practices to quickly isolate data across several columns.
Understanding why you might need to filter multiple columns in Google Sheets
In data analysis, it’s common to need multi-column filtering to narrow down records that meet several conditions at once. For example, you might want all rows where Status equals “Open” in column B and Priority equals “High” in column C, while also restricting the date in column D to a specific range. According to How To Sheets, mastering multi-column filtering saves hours of manual scanning and makes datasets much more actionable. This is especially true for students tracking assignments, professionals managing project tasks, and small business owners auditing inventory. When you filter across several columns, you’ll rely on logical operators and array contexts to combine criteria. The techniques covered here—FILTER, QUERY, and filter views—cover a broad spectrum of needs, from ad hoc checks to reusable templates that teammates can use consistently.
How To Sheets analysis shows that teams save significant time when applying consistent multi-criteria filters across shared datasets.
Tools & Materials
- Google account and access to Google Sheets(Needed to open and edit sheets in your browser)
- Spreadsheet with at least 3-4 columns(Headers in row 1; data starts from row 2)
- Internet connection(Stable connection for live formulas and sharing)
- Sample data or template(Optional for practice; helps demonstrate different criteria)
- Optional: named ranges(Helps make formulas easier to read and maintain)
Steps
Estimated time: 15-25 minutes
- 1
Identify the columns and criteria
Review your sheet and mark the columns that will participate in the filter (for example, Column B for Status and Column D for Date). Define the exact values or ranges you want to include (e.g., Status = 'Open' and Date after 2026-01-01). This preparation prevents misaligned formulas and reduces trial-and-error.
Tip: Document the criteria in a small note or use a named range to reference them. - 2
Choose a filtering method
Decide between FILTER for dynamic arrays, QUERY for a SQL-like approach, or a Filter View to save the criteria for others. Each method has trade-offs: FILTER is straightforward; QUERY offers advanced string matching; Filter Views preserve your view without changing the underlying data.
Tip: If you plan to share with teammates, consider Filter Views to avoid altering others’ work. - 3
Write a multi-criteria FILTER formula
Create a FILTER that combines criteria with logical operators. Example: =FILTER(A2:D100, (B2:B100="Open")*(D2:D100>=DATE(2026,1,1)), "No results"). This returns rows where Status is 'Open' and Date is on or after January 1, 2026.
Tip: Use parentheses and the * operator for AND logic; for OR, wrap criteria in a plus sign (+) or add separate FILTER layers. - 4
Write a multi-criteria QUERY formula
Use a QUERY to filter with SQL-like syntax. Example: =QUERY(A1:D100, "select A,B,C,D where B='Open' and D >= date '2026-01-01'", 1). This approach excels at text matching and sorting within a single formula.
Tip: Always include a header row parameter (1) if your sheet has headers to ensure column labels map correctly. - 5
Test with edge cases
Validate results when some criteria have missing values, mixed data types, or blank cells. Check how empty criteria impact results and adjust with IF conditions or COALESCE-style logic in Google Sheets.
Tip: Add a fallback message like 'No results' to clarify empty outputs. - 6
Save and share your filter setup
If using Filter Views, save the view and share it with collaborators so everyone sees the same filtered subset. With FILTER or QUERY, document the formula in your sheet or in a companion doc so teammates know how the results are produced.
Tip: Name each filter/view clearly and keep a short description of the criteria.
FAQ
What is the difference between FILTER and QUERY for multi-column filtering?
FILTER returns a dynamic array based on boolean criteria and is easy to read. QUERY uses SQL-like syntax and excels with complex text matching and sorting. Both can filter across multiple columns, but QUERY often offers more flexible string handling.
FILTER gives you straightforward multi-criteria results, while QUERY offers SQL-like flexibility for complex conditions.
Can I filter on non-adjacent columns?
Yes. Both FILTER and QUERY support multiple criteria across non-adjacent columns by referencing each column in its condition. You can combine conditions with AND/OR logic to tailor results.
You can filter across non-adjacent columns by combining criteria in FILTER or QUERY.
Does filtering affect hidden rows or only hide data?
Filtering hides rows based on your criteria but does not delete them. You can toggle filters to reveal all data without affecting the underlying sheet.
Filtering hides rows without deleting them; you can always show all data again.
How can I make text-based filters case-insensitive?
Use REGEXMATCH with a lower/upper normalization or (?i) for case-insensitive matching. This works well with both FILTER and QUERY for text fields.
Use REGEXMATCH with case-insensitive options to broaden text filters.
How do I share a multi-column filter with teammates?
Use Filter Views to save and share a specific view, or document the formula in a shared guide so others can reproduce the results.
Filter Views let teammates use the same filter without altering the data.
Watch Video
The Essentials
- Identify columns and criteria before building filters
- Choose FILTER, QUERY, or Filter Views based on needs
- Test with edge cases to ensure robust results
- Share or save views to maintain consistency
