FILTER Function in Google Sheets

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 the new, smaller output from the function. FILTER is often used with or instead of the SORT function when whittling down large amounts of data. FILTER is for reducing data and SORT is for, well, sorting it.

The filter function is typically accessed from the user menu, but it can also be typed into a cell just like any other function. Excel, the most popular spreadsheet program, only has it as a menu 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 filtered
condition1 – The criteria upon which the range is to be filtered
condition2... – The optional additional criteria upon which the range is to be filtered

Examples

Tip: When using FILTER, start your formula in a cell that should be the upper left most cell for the output. 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 and it can be changed.

Example 1

Using the FILTER function with one column of data. The first table is before the function is applied and the second is after.

Shows the starting data and the formula typed into the fifth row.
	A
1	2
2	5
3	3
4	3
5	`=FILTER(a1:a4,a1:a4<4)`
7
8

The first four rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
	A
1	2
2	5
3	3
4	3
5	2
7	3
8	3

Example 2

Let’s look at an example now of filtering a table with two columns of data using one condition.

Shows the starting data and the formula typed into the fifth row.
	A	B
1	2	c
2	5	c
3	3	hh
4	3	c
5	`=FILTER(a1:b4,b1:b4="c")`
6
7

The first four rows are the original data and the last three rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
	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

Three columns of data filtered by two conditions.

Shows the starting data and the formula typed into the fifth row.
	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

The first four rows are the original data and the last two rows are the dynamic results of the function. The function is still in cell A5 and you can see it if you select that cell.
	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 spreadsheeet for several examples of the FILTER function that you can study and use anywhere you would like.