How to Filter Data in Google Sheets

Understanding Google Sheets Filtering

Google Sheets offers two primary approaches to filtering your data. The first is the toolbar filter, which lets you click dropdown arrows on your column headers and select which rows to display. The second is the FILTER function, a formula that returns a filtered range based on conditions you define. Understanding when to use each method will save you time and make your spreadsheets more flexible. The toolbar filter works best when you want to quickly explore data or hide rows temporarily. The FILTER function shines when you need a dynamic, formula-driven approach that updates automatically as your source data changes.

Before diving into either method, you should organize your data with headers in the first row. Google Sheets relies on this structure to identify what you’re filtering. Your data should be contiguous, without empty rows or columns mixed in. If your data lives in a table format with proper headers, you’re already halfway to a smooth filtering experience.

Basic Filter: Turning It On

To create a basic filter in Google Sheets, select any cell within your data range and navigate to Data in the menu bar, then click Create a filter. Google Sheets will automatically detect your data range and add dropdown arrows to each column header. These dropdown arrows are your gateway to filtering. Click any arrow to see filtering options for that column.

When you click a column’s dropdown arrow, you’ll see all unique values in that column listed with checkboxes. By default, all values are checked, meaning all rows are visible. Uncheck any value to hide rows containing that value. For example, if your spreadsheet lists sales by region and you uncheck “West,” all rows with “West” in the Region column will disappear from view. The hidden rows aren’t deleted, they’re simply not displayed until you check “West” again.

Above the list of values, you’ll find additional filtering options. You can click “Clear” to show all values at once, or use the search box at the top to quickly find specific values if your list is long. The filter also lets you sort in ascending or descending order from this dropdown menu. You can sort A to Z or Z to A for text, or smallest to largest for numbers. This sorting applies only to that specific column and respects any filters you’ve already applied.

Filtering by Condition

The value-based filtering works well for simple “show me rows where the Region is North” scenarios. But what if you need rows where sales are greater than 10,000, or dates fall within the last 30 days? That’s where filter conditions come in. When you click a column dropdown and look for more advanced options, select “Filter by condition.” This opens a menu where you can define the exact criteria rows must meet.

Text columns support conditions like “contains,” “does not contain,” “equals,” and “does not equal.” If you’re filtering a Product Name column and want only rows containing “Premium,” you’d select “contains” and type “Premium.” The filter then shows only rows where the product name includes that word anywhere in the text. Number columns support “greater than,” “less than,” “equals,” “greater than or equal to,” “less than or equal to,” and “is between.” These conditions let you find all sales over a certain threshold or within a specific range.

Date columns offer conditions like “is before,” “is after,” “is on,” “is between,” and options to filter by specific date ranges like “today,” “yesterday,” “last 7 days,” or custom date ranges. You can stack multiple conditions within a single column using AND and OR logic. For instance, you might filter a Date column to show rows where the date is on or after January 1 AND on or before December 31 of a given year. Each condition you add narrows down the results further when using AND, or expands the results when using OR.

Filtering Multiple Columns at Once

Filtering becomes even more powerful when you apply filters to multiple columns simultaneously. When you filter Column A and then filter Column B, Google Sheets applies AND logic by default. This means only rows that satisfy both conditions will appear. For example, if you filter the Region column to show only “North” and the Product Type column to show only “Chairs,” you’ll see only rows where the region is North AND the product type is Chairs.

You can see at a glance which columns have active filters because the dropdown arrows will change color or display a small filter icon. This visual feedback helps you remember what you’re filtering. If you forget and expect to see all data, checking the filter icons on your column headers will remind you that filters are active. To view rows that don’t match your current filter criteria, you’ll need to adjust or remove the filters.

Filter Views for Safe, Shareable Filtering

Sometimes you want different people to see different filtered versions of the same data without permanently hiding rows from other collaborators. This is where filter views shine. A filter view is a saved, named filter that only affects what you (or specific users) see. Other people viewing the spreadsheet can choose to use a different filter view or see all data.

To create a filter view, select Data from the menu, then choose Filter views, then Create a new filter view. Give your filter view a descriptive name like “North Region Sales” or “High-Value Transactions.” Google Sheets will open the filter view in editing mode. Now when you apply filters, those filters are stored within this named view rather than affecting what everyone sees. You can see a list of your filter views and switch between them by clicking the filter view name in the toolbar at the top of your sheet.

Filter views are collaboration-friendly because each user can have their own filters without changing the underlying data or affecting what others see. If you’re working on a large spreadsheet with multiple users, you might create several filter views: one for Management to see all data, one for Sales to see only their region, and one for Finance to see only transactions above a certain amount. You can also share a specific filter view with collaborators by copying the URL of the filter view and sending it to them. When they open that URL, they’ll see the data filtered exactly as you set it up.

The FILTER Function

If you need a more dynamic, formula-based approach to filtering, the FILTER function is your tool. Unlike the toolbar filter, which you adjust manually, FILTER is a formula you write that automatically returns only the rows matching your criteria. The basic syntax is: =FILTER(range, condition1, [condition2], [condition3], and so on).

The first argument is your data range. This is typically your full dataset including headers, or sometimes just the data portion. The second argument is a condition that evaluates to TRUE or FALSE for each row. Rows where the condition is TRUE will appear in the FILTER output. You can add additional conditions separated by commas.

Here’s a practical example. Suppose you have a sales list in A1:D100 with columns for Date, Region, Product, and Sales Amount. To show only rows where the Region is “North,” you’d write: =FILTER(A1:D100, B1:B100=”North”). This tells Google Sheets to return all rows from your range where the corresponding value in column B equals “North.” The output appears as a new range starting where you entered the formula.

FILTER with Multiple Conditions

You can build more complex filters by combining multiple conditions. To apply AND logic, where both conditions must be true, you multiply the conditions together using an asterisk. For example: =FILTER(A1:D100, (B1:B100=”North”)*(C1:C100=”Chairs”)) returns only rows where Region is North AND Product is Chairs.

For OR logic, where either condition can be true, you add the conditions using a plus sign. The formula =FILTER(A1:D100, (B1:B100=”North”)+(B1:B100=”South”)) returns rows where Region is either North OR South. You can combine AND and OR logic in more complex formulas, though readability becomes important when conditions get lengthy. Breaking long FILTER formulas across multiple lines using Ctrl+Enter in the formula bar helps with readability.

You can also use comparison operators in conditions. For instance, =FILTER(A1:D100, D1:D100>10000) returns only rows where the Sales Amount is greater than 10,000. The operators work the same way as in basic filter conditions: greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), equals (=), and does not equal (<>).

FILTER with SORT

The FILTER function returns rows in their original order. If you want filtered results sorted in a specific way, you can wrap your FILTER formula inside a SORT function. The syntax becomes: =SORT(FILTER(range, conditions), column_index, is_ascending).

For example, if you want to filter for North region sales and then sort by Sales Amount from highest to lowest, you’d write: =SORT(FILTER(A1:D100, B1:B100=”North”), 4, FALSE). The 4 refers to column D (the fourth column in your range), and FALSE means descending order (highest to lowest). Change FALSE to TRUE for ascending order. This combination lets you create dynamic, always-up-to-date lists that are both filtered and sorted.

Dynamic Filter with Dropdown

You can take interactivity further by combining FILTER with a Data Validation dropdown. This creates a dashboard-like experience where users select from a dropdown menu, and the filtered results update instantly. First, create a dropdown list in a cell using Data Validation (Data > Validation > Create a list). This dropdown might contain region names: North, South, East, West.

Then write your FILTER formula to reference that dropdown cell. If your dropdown is in cell G2, your formula becomes: =FILTER(A1:D100, B1:B100=G2). Now when users change the dropdown selection, the FILTER formula automatically updates to show only rows matching the selected value. You can add multiple dropdowns for different columns and reference all of them in one complex FILTER formula, creating a multi-criteria dashboard where users filter by Region and Product simultaneously.

Clearing Filters

When you no longer need a filter, you have options. To remove filters from individual columns without turning off the entire filter, click the column dropdown and select “Clear filter.” This removes that column’s filter while keeping filters on other columns active. To remove all filters at once, go to Data in the menu and click Remove filter. This will turn off the toolbar filter entirely, and all rows will become visible again.

For FILTER formulas, you simply delete the formula cell to stop filtering. If the FILTER formula spilled across multiple cells (because the result was wide or tall), deleting the formula cell in the top-left will remove the entire result. After deleting, you might want to adjust your data layout to use the space that the formula was occupying.

Common Filtering Issues

One frequent problem is accidentally hiding rows you need and forgetting about them. Always check your column dropdown arrows for filter indicators before assuming you’re seeing all data. If your FILTER formula returns no results, verify that your conditions match values in your data. Empty cells or slightly different formatting can cause mismatches. For instance, filtering for “North” won’t match “north” in lowercase, so check your case sensitivity.

The #N/A error in a FILTER formula typically means no rows matched your conditions. The #REF! error usually means you referenced a range incorrectly. If your filter isn’t applying to new rows you add below your original data, remember that the toolbar filter only applies to the range it detected when you created it. Adding rows below that range won’t automatically be included. You’ll need to reapply the filter to include the new rows, or use a FILTER formula which will include them if you wrote the formula to cover extra rows.

Another common issue: your FILTER formula works fine, but when other users open the sheet, they see different results. This happens because FILTER can depend on helper columns or named ranges that other users don’t have access to. Document your filter logic clearly so collaborators understand what’s being shown. If you’re using a filter view and sharing it with collaborators, remember that some might not have editing permissions to modify the filter view itself.

Combining Filters with Other Google Sheets Features

Filtering pairs beautifully with other Google Sheets features. You can sort by date in Google Sheets after filtering to reorganize results. You can use conditional formatting in Google Sheets to highlight filtered results that meet additional criteria. You can create a graph in Google Sheets that visualizes only your filtered data.

If you’re working with formulas that aggregate data, the SUMIF in Google Sheets function and COUNTIF in Google Sheets function let you sum or count only rows that meet certain criteria. These functions work independently of your filters, meaning they’ll count or sum based on their own conditions regardless of what your toolbar filter is hiding. For truly dynamic reporting, pivot tables in Google Sheets offer another layer of data analysis beyond simple filtering.

If you’re pulling data from multiple sheets, the IMPORTRANGE in Google Sheets function can bring external data in, which you can then filter. The ARRAYFORMULA in Google Sheets function extends your filter conditions across multiple rows without repeating the formula. And when you’re preparing filtered data for sharing, share a Google Sheet with specific permissions so collaborators see only what they need.

Filtering for Data Quality and Cleanup

Beyond viewing subsets of data, filtering helps you identify and fix data quality issues. Use filters to find empty cells, duplicates, or unusual values. If you’re removing duplicates in Google Sheets, filtering first to identify suspected duplicates helps you verify they’re actually duplicates before removing them. You can filter to show only rows with empty values in a critical column, then fill those cells with the correct data.

Filtering also supports data validation workflows. Create a filter to show rows where a Status column equals “Pending Review,” then systematically work through them. Once reviewed, change the status to “Approved” and they’ll automatically disappear from your filtered view, confirming you’ve completed that step. This workflow pattern works for any multi-step process: initial entry, review, approval, completion.