6 Ways to Filter Data in Google Sheets

Last Updated on January 10, 2024 by Jake Sheridan

In this guide, you will learn all about filtering in Google Sheets.

How do I use filters in Google Sheets?

Using the Filter tool in Google Sheets is a straightforward process that allows you to display only the rows that meet certain criteria, making it easier to analyze data.

Compared to the FILTER function, the filter tool is a more straightforward and visual way of filtering your data.

Here’s a step-by-step guide on how to create and use filters using the Filter tool:

Step 1

Open the Google Sheets document containing the data you want to filter.

Ensure your data is organized in a table-like structure, ideally with headers.

 

Step 2

Click and drag to select the range of cells you want to filter.

You can select specific columns or the entire range of your dataset.

If your data has headers, include them in the selection.

 

Step 3

With the range selected, click on the “Data” menu at the top of the screen, and then select “Create a filter.”

 

This action will apply filter controls (displayed as an inverted triangle made of three lines) to each header cell in your selected range.

 

Step 4

Click on the filter icon in any of the header cells.

A dropdown menu will appear, showing several filtering options.

You can choose to filter by condition.

This option contains a variety of ways to compare your data with some criteria.

You can choose from various conditions like “text contains,” “date is,” “greater than,” and so on.

Google Sheets also allows you to filter by the values themselves.

You can manually check or uncheck items (which are based on the unique values of the given column) to show or hide certain rows in your dataset.

After setting up your criteria, click on OK to proceed.

The filtered range should now show all rows that follow the specified criteria.

In the example above, we added a filter to our range to show all rows that have a shirt size of “2XL”.

Step 5

To clear a filter and show all data again, click the filter icon in the header and select “None” if you are Filtering by condition or click “Select all” if you are filtering by values.

To completely remove the filter functionality, go to “Data” and select “Remove filter.”

 

1. How to filter in Google Sheets by Text

There are multiple ways you can filter a dataset by text using the FILTER function.

Here is a detailed breakdown of each method of filtering through text.

Exact Match

We can use the “=” equal operator to set up a condition that only accepts exact matches.

Sample Formula:

=FILTER(A2:E101,A2:A101=”XL”)

Sample Usage:

This formula filters the range A2:E101 to show rows where the shirt size (column A) is equal to “XL”.

After evaluating our formula, only rows with “XL” in column A will be displayed.

Inverse Exact Match

We can use the “<>” operator to set up a condition that only accepts results that do not match the specified string.

Sample Formula:

=FILTER(A2:E101,A2:A101<>”M”)

Sample Usage:

Here, the formula filters the range A2:E101 to display rows where the value in column A does not exactly match “M”.

In the table above, all rows except those with “M” in column A will be shown.

Text Contains

We can use the SEARCH function inside our FILTER function to filter a range for cells that contain a specific string.

Sample Formula:

=FILTER(A2:E101, ISNUMBER(SEARCH(“Red”, B2:B101)))

Sample Usage:

This formula filters the range A2:E101 for rows where the value in column B contains the text “Red” anywhere within it.

Since the SEARCH function returns a number when a match is found, we’ll wrap our SEARCH function with ISNUMBER to return either TRUE or FALSE.

Text Does Not Contain

We can use the SEARCH function again to find all rows where the text does not contain a certain string.

Sample Formula:

=FILTER(A2:E101, ISERROR(SEARCH(“Red”, B2:B101)))

Sample Usage:

The formula filters the range A2:E101 to show rows where the value in column B does not contain the text “Red”.

Since the SEARCH function returns a #N/A error when no matches are found, we’ll wrap our function with ISERROR to ensure that only rows where there are no matches are returned.

In the table above, our FILTER formula excludes any row with values that include “Red”.

Case-Insensitive Match

One possible limitation of the Exact Match formula shown earlier is that it is case-sensitive.

This can be an issue if your data contains both uppercase and lowercase variations of the same string.

For example, if we want to match both “red” and “Red”, we’ll need to adjust our formula to facilitate a case-insensitive match.

Sample Formula:

=FILTER(A2:E101, LOWER(B2:B101)=”red”)

Sample Usage:

This formula filters the range A2:E101 for rows where the value in the color field (column B), when converted to lowercase, matches “red”.

It ensures a case-insensitive match, allowing our output to display rows with “red”, “RED”, or “Red”.

REGEXMATCH

For more advanced text filtering, you may need to use the REGEXMATCH function.

This function allows you to use regular expressions to match particular patterns for text.

Sample Formula:

=FILTER(A2:E101, REGEXMATCH(B2:B101,”^(\w+\s+\w+).*$”))

Sample Usage:

This formula uses REGEXMATCH to filter the range A2:E101 for rows where the value in column B exactly matches the regular expression pattern “^(\w+\s+\w+).*$”.

This pattern matches any text with at least two words.

In the table above, our FILTER function returns rows that include at least two words in the color column.

2. How to filter by date and time in Google Sheets

With the FILTER function, we can filter our data by date and time in a variety of ways.

Here are some of the most common methods you can use to filter datasets with date and time values.

Filter by specific date

We can use the FILTER function to filter a dataset to show rows where a specific column is equal to a specific date.

We can simply set the condition to check if a column is equal to a specific date.

Formula:

=FILTER(A2:C363,A2:A363=DATE(2023,1,1))

Sample Usage:

This formula filters the range A2:C363 for rows where the date in column A exactly matches January 1, 2023.

Since the date could vary in format, we’ll use the DATE function to create a datetime value to use for checking.

Filter by Specific Month or Year

To filter by a specific month or year, we can use the MONTH and YEAR functions to extract the month or year from a date column.

Sample Formula:

=FILTER(A2:C363,MONTH(A2:A363)=3,YEAR(A2:A363)=2023)

Sample Usage:

Here, the formula filters the range A2:C363 for rows where the date in column A is in March 2023.

It uses the MONTH and YEAR functions to match rows with any date in January 2023.

In our example above, we were able to filter the original dataset to just entries dated in March 2023.

Filter Where Date is Before

We can use the “<” comparison operator to compare two dates, particularly whether adate column value comes before some specified date.

Sample Formula:

=FILTER(A2:C363,A2:A363<DATE(2023,1,10))

Sample Usage:

The formula above filters the range A2:C363 for rows where the date in column A is before January 10, 2023.

You can replace the “<” operator with “<=” if you also want to include dates equal to the date you’re using as a criteria.

Filter Where Date is After

Similar to the less than operator (“<”), we can use the greater than comparison operator (“>”) to compare two dates, particularly whether a date column value comes after some specified date.

Sample Formula:

=FILTER(A2:C363,A2:A363>DATE(2023,6,1))

Sample Usage:

This formula filters our sample dataset to display rows where the date in column A is after June 1, 2023.

You can replace the “>” operator with “>=” if you also want to include dates equal to the date you’re using as a criteria.

Filter Where Date is Between Date1 and Date2

We can use two filter criteria to display rows where the date is between two dates or falls within a certain date range with a start date and end date.

We’ll first check if the date comes after the start date then we’ll check if the date comes before the end date.

Formula:

=FILTER(A2:C363,A2:A363>=DATE(2023,1,10),A2:A363<=DATE(2023,1,15))

Sample Usage:

The formula filters the range A2:C363 for rows where the date in column A is between January 10, 2023, and January 15, 2023.

We can replace the “>=” operator with “>” and the “<=” operator with “<” to create a filter that excludes the start date and end date.

Filter for a specific time

We can use the HOUR function to determine whether a timestamp occurs in a specific hour.

Formula:

=FILTER(A2:A11,HOUR(A2:A11)=9)

Sample Usage:

The formula filters the range A2:A11 for rows where the timestamp occurs at the 9th hour regardless of date.

We can include another criteria that extracts the minute component of the timestamp using the MINUTE function.

In the example above, we used HOUR and DATE together to find all timestamps that fall within the first minute of the 9th hour of the day.

3. How to filter in Google Sheets using cell references

We can make our FILTER function more dynamic by using cell references in our criteria rather than manually typing a string or value.

With cell references, we can quickly change filter criteria bychanging the value in a cell rather than editing a function.

Suppose you have a table with sales data in columns A through C, with the category in column B.

You want to filter this list to show only the entries that match a specific category, and you want to be able to change the category as needed easily.

Now, you use the FILTER function like this:

=FILTER(A2:C16,B2:B16=F1)

This formula filters the range A2:C16 based on the criterion that the value in column B must match the string set in cell F1.

In our current example, if F1 has the category “Laptop”, the formula will display all rows from our target range where the category is “Laptop”.

Whenever you change the category in F1, the filtered results will update automatically to reflect the new criterion.

4. Google Sheets FILTER formulas with multiple criteria

Here’s how to set up a FILTER formula so that it considers multiple criteria:

Step 1

Open your Google Sheets document where you want to use the FILTER formula.

Identify the range of data you want to filter and the multiple criteria you wish to apply.

In our current example, we want to filter for rows where the order quantity is less than 10 but over 1 in quantity.

Step 2

Click on the cell where you want the filtered results to appear.

This is where your FILTER formula will be entered and where the filtered data will be displayed.

In the image above, we’ll select cell F2 for displaying our filtered results.

Step 3

Start typing the FILTER formula.

Begin with =FILTER( and then specify the range of cells you want to filter.

Step 4

Next, we’ll need to add the first criteria for our FILTER formula.

After the data range, type the condition that must be met for the first criteria.

For example, to filter for orders with quantities less than 10, we can add the criterion C2:C101<10.

This criterion will filter out all the rows from A2:D101 where the quantity in column C is less than 10..

Step 5

After the first criteria, type the condition for the second criteria.

In our example, we’ll use the criteria C2:C101<>0 to exclude all rows where the quantity is equal to zero.

Step 6

Close the formula with a parenthesis and press Enter.

The cell will now display the rows from your specified range that meet both criteria.

Step 7

So far, adding new arguments to the FILTER function follows the AND logic, meaning that all criteria must be true for a row to be included.

What should we do if we want to use OR logic instead for our criteria?

For example, you may want to include a row if the order quantity is exactly 7 or greater than 18.

For OR logic, we can use the “+” symbol between conditions:

=FILTER(A2:D101,(C2:C101>18)+(C2:C101=7))

In the example above, we may have a single criterion argument but the argument is actually two conditions combined with a “+” character which acts as an OR operator.

Using this formula, we can filter to display just the rows in our dataset that pass either of these conditions.

5. Add filter to Google Sheets to multiple columns

Sometimes you may need to add a filter that considers more than one column in a data range.

To apply a filter in Google Sheets that uses different columns for each criteria argument, you can use a combination of conditions in the FILTER formula.

Let’s take a look at a simple example that demonstrates this approach.

Suppose you have a dataset of T-shirt orders with details such as shirt size, order quantity, and shirt color.

You want to filter this data based on two criteria: shirt size must be “S” and quantity must be greater than 10.

The formula would look like this:

=FILTER(A2:D101,A2:A101=”S”,C2:C101>15)

This formula filters the range A2:D101 where the shirt size in column A is equal to “S” and the quantity in column C is greater than 50.

6. How do I create a drop-down filter in Google Sheets?

Creating a drop-down filter in Google Sheets involves using data validation to create a drop-down list for criteria selection and then applying a filter function that references the selected criteria.

Here’s a step-by-step guide to setting this up:

Step 1

First, you’ll need to decide the criteria for your drop-down list.

For instance, if you want to filter a list of T-shirt orders by shirt size, list all shirt size options in a separate column or sheet.

In this example, we’ll list all available shirt size options in a sheet labeled Shirt_Sizes.

Step 2

Next, use data validation to create a drop-down list based on the criteria range.

Click on the cell where you want the drop-down menu to appear. This is where you’ll select your filter criterion.

Click on Data in the menu bar above, then select Data validation.

In the Data validation rules sidepanel, click Add a new rule.

Under the Criteria option, select Dropdown (from a range).

Next, input the range containing the values you wish to use for the dropdown range.

Click Done to proceed.

Now, the selected cell will have a drop-down menu containing your criteria list.

Step 3

Now, apply a filter function that uses the value selected from the drop-down list as its criterion.

In our example above, we used the formula =FILTER(A2:C101,A2:A101=F1) to filter for rows where the value in column A is equal to the chosen value in cell F1.

Step 4

Select different criteria from the drop-down menu.

The data in your FILTER function range should update automatically to reflect the selected criterion.

What are the conditions for filtering in Google Sheets?

In Google Sheets, filtering allows you to view specific rows in a dataset while temporarily hiding the others based on certain conditions.

These conditions determine which rows will be visible after the filter is applied.

Here are some of the most common conditions for filtering in Google Sheets.

Cell Content Conditions

  • Is empty: This condition displays all rows with empty cells.
  • Is not empty: This condition shows all rows with non-empty cells.

Text Conditions

  • Text is exactly: This condition shows rows where cells exactly match the specified text.
  • Text contains: This condition shows rows containing a specified substring.
  • Text does not contain: This condition hides rows containing a certain substring.
  • Text starts with: This condition will show rows where cell text begins with some specified string of characters.
  • Text ends with: This condition will display rows where cell text ends with some specified string of characters.

Number Conditions

  • Greater than, less than: This condition shows rows where numbers are greater or less than a specified value.
  • Greater than or equal to, less than or equal to: This condition displays rows where numbers meet or exceed, or are less than or equal to a specified value.
  • Equal to: This condition displays rows where numbers exactly match the specified value.
  • Not equal to: This condition hides rows where numbers match the specified value.
  • Between: This condition shows rows where numbers fall within a specified range.
  • Not between: This condition hides rows where numbers fall within a certain range.

Date Conditions

  • Date is: This condition shows all rows that match a specified date.
  • Before/After a certain date: This condition shows rows where dates fall before or after the specified date.

Custom Formulas

You can use a custom formula to set more complex and specific conditions for filtering. The rows for which the formula returns TRUE will be displayed.

For example, we can use the custom formula =MOD(C2:C101,2) to output all rows where the value in column C is odd.

Since the MOD function above always returns 1 if the column value is odd, only rows with odd values will appear.

We can use the formula =NOT(MOD(C2:C101,2)) to return all even numbers instead.

Can you auto filter in Google Sheets?

Yes, it is possible for a filter to automatically update in Google Sheets, however it will depend on the method you use to filter your data.

Suppose you use the Filter tool to show only rows where a column has a TRUE value present.

If we change one of these values to FALSE, the row will still remain visible.

Any changes are made in a filtered range, you will need to manually apply your desired filter again.

However, data from the FILTER function will automatically update when the source data is modified.

Unlike the Filter tool, the FILTER function is dynamic and can update its output in real-time.

Summary

Hopefully this guide has given you an overview of filtering

data in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: