If you are a regular spreadsheet user, the FILTER function in Google Sheets is something you need to learn.
This powerful function will take a dataset and only return the rows of data that meet certain criteria that you specify in the formula.
In this tutorial, I will show you how to use the FILTER function in Google Sheets.
Table of Contents
FILTER Function Syntax
The FILTER function will return a filtered version of a range where the rows or columns meet certain conditions that you specify in the formula.
The syntax of the FILTER function is:
FILTER(range, condition1, [condition2, …])
- range – this is the data that will be filtered
- condition1 – this argument can either be an array formula that evaluates to true or false or a column or row that contains true or false values that corresponds to the first column or row in range
- condtion2 – this argument is optional. You can add additional conditions here
Important notes about this function
- If no values are found that satisfy the conditions, an #N/A error will be returned
- This function can only filter rows or columns at one time. If you want to filter both rows and columns, you have to filter the returned value of one FILTER function by using it as the range in another FILTER function
How to Use the FILTER Function
Using FILTER with a Single Condition
Now that we have covered the basics of the syntax, let’s take a look at how to use the FILTER function.
In the example above, I have test scores for various students. What I want to do is filter the results so that only the students with a test score above 60 are returned.
In this example, my FILTER formula is placed in cell E2. The formula is:
=FILTER(A2:C14,C2:C14>60)
Let’s break down each argument to understand what it is doing:
- A2:C14 – this is the range that will be filtered
- C2:C14>60 – this is the condition. If the values in this range are greater than 60, the row will be returned in the filtered results
So what is displayed in the filtered results that are returned are only lines where the test score is greater than 60.
Let’s take a look at another example so we can really make sure we understand how to use FILTER
In this example, I have the same dataset as the previous example, but this time my FILTER function is set to only return results where the gender of the student is female. The formula used in this example is:
=FILTER(A2:C14,B2:B14=“Female”)
Using FILTER with Multiple Criteria
You can also filter by multiple criteria with the FILTER function.
Let’s take a look at this in an example:
In this example, I have the same dataset with test scores for various students. This time I want to create a filter using the FILTER function that will return only female students with test scores above 70.
The formula used in this example is:
=FILTER(A2:C14,B2:B14=“Female”,C2:C14>70)
To filter by multiple criteria you just add extra conditions onto the end of the formula. By using multiple conditions you can really narrow down a dataset to just the important information that you want to look at.
Closing Thoughts
The FILTER function is a powerful tool that every spreadsheet user should know. Being able to quickly filter and sort through data to get the information you need is something that will greatly increase your productivity as a regular spreadsheet user.
I have only covered the basics of the FILTER function here, but there are certainly other ways you can use it as you get more comfortable with using the function.
One thing I didn’t cover is filtering the results of a FILTER function, which is definitely possible. So you may want to play around with that in your own spreadsheet by having multiple FILTER functions to really narrow down your results.
More Google Sheets Tutorials:
How to Get the Last Value in a Column
How to Use Wildcards
How to Hide a Row Based On Cell Value
FILTER Has Mismatched Range Sizes