Using FILTER in Google Sheets

In a previous post I discussed performing database-style queries on data in your sheets. QUERY is a very powerful and flexible tool for selecting data, but some jobs can be done a little more simply using Google Sheets’ FILTER function.

Filter has the general syntax

FILTER(range, condition1, [condition2, ...])

where range specifies which cells in the sheet are to supply the source data, and condition1 (and any further, optional conditions) determine which cell values will be selected by the filter.

Take a look at this example sheet.

Click image to enlarge

Click image to enlarge

Suppose we want to select only those students scoring more than 75, and post their names and scores to columns D and E. We can use a FILTER expression in cell D2:

Click image to enlarge

Click image to enlarge

Here, our range parameter is A2:B6 (the source range names and scores), and our only condition is that the scores in column B are greater than 75.

FILTER can only be applied to either rows or columns at one time. To filter both rows and columns in one operation, use the value returned by one FILTER operation as the range argument in another.