How to Filter Rows in Google Sheets: Complete Guide with Examples | SheetXAI

The Problem

You have a large dataset in Google Sheets and need to show only rows that meet specific criteria. You want to filter out irrelevant data to focus on what matters, whether it's filtering by value, text, date, or multiple conditions.

The Easy Way: Use SheetXAI

If you don't want to manually set up filters, the fastest way is to simply ask SheetXAI.

With SheetXAI, you can open the sidebar and type:

Filter rows where column A is greater than 100 and column B equals "Active".

SheetXAI will instantly filter your data based on your criteria. You can use natural language to describe complex filtering conditions, and SheetXAI will handle the implementation.

The Manual Way: Step-by-Step Methods

There are several ways to filter rows in Google Sheets. Here are the most effective methods:

Method 1: Using the Filter Feature (Easiest)

Best for: Interactive filtering where you want to see and change filters easily.

Steps:

1. Select your data: Click on a cell within your data range, or select the entire range you want to filter

2. Create a filter:

   • Click Data in the menu bar
   • Select Create a filter
   • You'll see filter icons (funnel icons) appear in the header row

3. Apply a filter:

   • Click the filter icon in the column you want to filter by
   • You'll see a menu with filtering options:
     • Filter by condition: Set up custom conditions (greater than, less than, contains, etc.)
     • Filter by values: Check/uncheck specific values to show or hide
     • Search: Type to search for specific values

4. Set filter criteria:

   • For Filter by condition: Choose a condition (e.g., "Greater than") and enter a value
   • For Filter by values: Uncheck values you want to hide, check values you want to show
   • Click OK

5. View filtered results: Only rows meeting your criteria will be visible. Other rows are hidden (not deleted).

6. Add more filters: You can filter multiple columns at once by clicking filter icons in other columns

7. Remove filter: Click Data → Remove filter to show all rows again

Example Filter Conditions:

• Greater than: Show rows where value > 100
• Less than: Show rows where value < 50
• Equal to: Show rows where value = "Complete"
• Contains: Show rows containing specific text
• Does not contain: Hide rows containing specific text
• Begins with: Show rows starting with specific text
• Ends with: Show rows ending with specific text

Method 2: Using FILTER Function (Formula-Based)

Best for: Creating filtered views that update automatically, or when you want filtered data in a different location.

What it does: Returns a filtered range based on criteria.

Syntax: =FILTER(range, condition1, [condition2], ...)

Parameters:

• range: The range of data to filter
• condition1: The first filtering condition (must be TRUE/FALSE array)
• [condition2]: Additional conditions (optional)

Example:

=FILTER(A2:C100, B2:B100 > 100, C2:C100 = "Active")

This filters rows where column B > 100 AND column C = "Active".

How it works:

1. The function evaluates each row against the conditions
2. Returns only rows where all conditions are TRUE
3. The result is a dynamic array that updates automatically

Use case: When you want filtered data to appear in a different location or update automatically.

Method 3: Using QUERY Function (Advanced)

Best for: Complex filtering with SQL-like syntax.

What it does: Uses Google Visualization API Query Language to filter and manipulate data.

Syntax: =QUERY(data, query, [headers])

Parameters:

• data: The range of data to query
• query: The query string in Google Visualization Query Language
• [headers]: Number of header rows (optional, default is 1)

Example:

=QUERY(A1:C100, "SELECT * WHERE B > 100 AND C = 'Active'", 1)

This uses SQL-like syntax to filter rows.

Common Query Syntax:

• SELECT * WHERE B > 100: Filter where column B > 100
• SELECT A, B WHERE C = 'Active': Select columns A and B where C = 'Active'
• SELECT * WHERE B > 100 ORDER BY B DESC: Filter and sort

Use case: When you need complex filtering with sorting, selecting specific columns, or aggregations.

Understanding Filter Logic

The key to filtering rows correctly is understanding how conditions work:

1. Single condition: Shows rows where the condition is TRUE
2. Multiple conditions: Can use AND logic (all must be true) or OR logic (any can be true)
3. Text filtering: Can filter by exact match, contains, begins with, ends with
4. Number filtering: Can filter by comparisons (>, <, =, >=, <=, <>)
5. Date filtering: Can filter by date ranges, before/after dates

Important: Filtering hides rows but doesn't delete them. The data remains in your sheet.

Common Filter Scenarios

Filter Rows by Text Value

To show only rows where a column equals specific text:

Using Filter Feature:

1. Click filter icon in the column
2. Uncheck "Select all"
3. Check only the values you want to see
4. Click OK

Using FILTER Function:

=FILTER(A2:C100, B2:B100 = "Complete")

Filter Rows by Number Range

To show rows where a number falls within a range:

Using Filter Feature:

1. Click filter icon
2. Choose "Filter by condition"
3. Select "Between" or use "Greater than" and "Less than" separately

Using FILTER Function:

=FILTER(A2:C100, (B2:B100 >= 10) * (B2:B100 <= 20))

Filter Rows by Date

To filter rows by date:

Using Filter Feature:

1. Click filter icon in date column
2. Choose "Filter by condition"
3. Select date condition (Before, After, Between, etc.)
4. Enter date values

Using FILTER Function:

=FILTER(A2:C100, B2:B100 >= DATE(2024,1,1))

Filter Rows with Multiple Conditions (AND)

To filter rows that meet multiple conditions:

Using Filter Feature:

1. Apply filter to first column
2. Apply filter to second column
3. Both conditions must be met (AND logic)

Using FILTER Function:

=FILTER(A2:C100, (B2:B100 > 100) * (C2:C100 = "Active"))

The * operator means AND (both conditions must be true).

Filter Rows with Multiple Conditions (OR)

To filter rows that meet any of multiple conditions:

Using FILTER Function:

=FILTER(A2:C100, (B2:B100 > 100) + (C2:C100 = "Active"))

The + operator means OR (either condition can be true).

Note: The Filter Feature uses AND logic when multiple columns are filtered. For OR logic, use the FILTER function.

Filter Rows Containing Specific Text

To show rows where a cell contains specific text:

Using Filter Feature:

1. Click filter icon
2. Choose "Filter by condition"
3. Select "Contains" or "Text contains"
4. Enter the text to search for

Using FILTER Function:

=FILTER(A2:C100, REGEXMATCH(B2:B100, ".*Complete.*"))

Or simpler:

=FILTER(A2:C100, ISNUMBER(SEARCH("Complete", B2:B100)))

Filter Out Blank Rows

To show only rows that have data:

Using Filter Feature:

1. Click filter icon
2. Uncheck "(Blanks)"

Using FILTER Function:

=FILTER(A2:C100, A2:A100 <> "")

Advanced Filtering Techniques

Filter Based on Another Sheet

To filter data based on criteria from another sheet:

=FILTER(Sheet1!A2:C100, Sheet1!B2:B100 > Sheet2!A1)

This filters Sheet1 data where column B is greater than a value in Sheet2.

Filter and Sort Combined

Using QUERY function to filter and sort:

=QUERY(A1:C100, "SELECT * WHERE B > 100 ORDER BY B DESC", 1)

This filters rows where B > 100 and sorts by column B in descending order.

Filter Unique Rows

To show only unique rows after filtering:

=UNIQUE(FILTER(A2:C100, B2:B100 > 100))

Filter with Case-Sensitive Text

To filter text with case sensitivity:

=FILTER(A2:C100, EXACT(B2:B100, "Complete"))

Filter Rows with Errors

To filter out rows containing errors:

=FILTER(A2:C100, NOT(ISERROR(B2:B100)))

Common Mistakes to Avoid

1. Forgetting to include headers: When using FILTER function, make sure your range includes all columns you want to display

2. Using wrong operator: Use * for AND and + for OR in FILTER function conditions

3. Not updating ranges: If you add new data, update your FILTER function range or use entire columns (A:A instead of A2:A100)

4. Filtering and then editing: Be careful when editing filtered data - you might be editing hidden rows

5. Case sensitivity: Text filters are case-insensitive by default. Use EXACT() for case-sensitive filtering

Tips for Better Row Filtering

• Use Filter Feature for exploration: It's great for interactively exploring your data

• Use FILTER function for reports: When you need filtered data in a specific location that updates automatically

• Combine filters: You can use multiple filter icons to create complex AND conditions

• Save filter views: Use "Filter views" (Data → Filter views → Create new filter view) to save different filter configurations

• Use SheetXAI for complex filters: When filtering logic gets complicated, SheetXAI can generate the right formula automatically

• Test your filters: Always verify your filter shows the expected rows

Filter Views (Advanced Feature)

Google Sheets allows you to save multiple filter configurations:

1. Create a filter view:

   • Click Data → Filter views → Create new filter view
   • Set up your filters
   • Give it a name

2. Switch between views: Access saved filter views from the filter views menu

3. Share filter views: Others can see your filter views without affecting their view

Use case: When different people need different filtered views of the same data.

Filtering vs Sorting

Filtering: Hides rows that don't meet criteria (data still exists, just hidden)

Sorting: Reorders all rows but shows everything

You can combine both: Filter first to show only relevant rows, then sort those filtered rows.

Conclusion

Now you know multiple ways to filter rows in Google Sheets. The Filter Feature is perfect for interactive filtering, while the FILTER function is great for formula-based filtering that updates automatically. The QUERY function offers the most flexibility for complex scenarios.

But for those times when you want to filter rows quickly without thinking about formulas or filter settings, SheetXAI can filter your data with simple natural language commands. Just describe what you want to see, and SheetXAI will handle the filtering automatically.

Related Guides

• How to Count Rows in Google Sheets - Learn different methods to count rows with data
• How to Count Cells in Google Sheets - Count cells with data, numbers, or specific criteria
• How to Delete Blank Rows in Google Sheets - Remove empty rows to clean up your spreadsheet
• Google Sheets AI Guide - Learn how AI can automate your Google Sheets workflows
• AI Spreadsheet Tools - Discover how AI transforms spreadsheet work