The FILTER function allows you to narrow down a range of data to include only items that meet the criteria that you specify. The function creates a new range of data with a new, smaller output. When whittling down large amounts of data, you often use FILTER with or instead of the SORT function. In other words, FILTER is for reducing data, and SORT is for sorting it.
You typically access the filter function from the user menu, but you can also type it into a cell just like any other function.
Video Explanation
Purpose
Returns data from your specified source with only the criteria that you selected. Yes, it gets rid of what you don’t want.
Syntax
=FILTER(range,condition1,[condition2...])
Basic information about the inputs:
range– The entire range of data that is to be filteredcondition1– The criteria upon which the range is to be filteredcondition2...– The optional additional criteria upon which the range is to be filtered
Examples
Tip: This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper-left cell.
Example 1
Firstly, we are using the FILTER function with one column of data. The first table is before the function is applied, and the second is after.
| A | |
| 1 | 2 |
| 2 | 5 |
| 3 | 3 |
| 4 | 3 |
| 5 | =FILTER(A1:A4,A1:A4<4) |
| 7 | |
| 8 |
| A | |
| 1 | 2 |
| 2 | 5 |
| 3 | 3 |
| 4 | 3 |
| 5 | 2 |
| 7 | 3 |
| 8 | 3 |
Example 2
Secondly, let’s look at an example now of filtering a table with two columns of data using one condition.
| A | B | |
| 1 | 2 | c |
| 2 | 5 | c |
| 3 | 3 | hh |
| 4 | 3 | c |
| 5 | =FILTER(A1:B4,B1:B4="c") | |
| 6 | ||
| 7 |
| A | B | |
| 1 | 2 | c |
| 2 | 5 | c |
| 3 | 3 | hh |
| 4 | 3 | c |
| 5 | 2 | c |
| 6 | 5 | c |
| 7 | 3 | c |
Example 3
Lastly, we filtered these three columns of data by two conditions.
| A | B | C | |
| 1 | 2 | c | 3 |
| 2 | 5 | c | 2 |
| 3 | 3 | hh | 3 |
| 4 | 3 | c | 3 |
| 5 | =FILTER(A1:C4,B1:B4="c",C1:C4=3) | ||
| 6 | |||
| A | B | C | |
| 1 | 2 | c | 3 |
| 2 | 5 | c | 2 |
| 3 | 3 | hh | 3 |
| 4 | 3 | c | 3 |
| 5 | 2 | c | 3 |
| 6 | 3 | c | 3 |
Live examples in Sheets
Go to this spreadsheet for several examples of the FILTER function that you can study and use anywhere you would like.