Filtering Data in Google Spreadsheets

Knowing how to filter data stores is a pretty basic requirement when dealing with data - whether you’re working with databases or with spreadsheets.

Typically, in a database, you’d use an SQL query to filter your data. With Google spreadsheets on the other hand, you have a few methods to choose from.

In this post, I’ll introduce two methods you can use in Google Spreadsheets: the QUERY function and the FILTER function.

The QUERY Function

The QUERY() function is a powerful tool that allows you to filter, sort, and manipulate data in a spreadsheet.

Specifically, it runs the Google Visualisation API Query Language to allow you to use SQL-like syntax and commands to filter your data. So if you’re from the SQL world like I am, it’s pretty easy to get started with filtering data in Google Spreadsheets this way.

QUERY Function Syntax

The syntax for the QUERY function is as follows:

=QUERY(range, query, [headers])

The range parameter is the range of cells you want to query. This can be a range of cells, or an entire sheet.

For example, “A1:B10” would be a range of cells from A1 to B10, and “Sheet1!A1:B10” would be the same range of cells, but specifically from the sheet named Sheet1.

The query parameter is the SQL-like query string you want to run on the range of data. This is where you can use the Google Visualisation API Query Language to filter, sort, and manipulate your data.

For example, if you wanted to select all the rows from the data where the value in column A is greater than 10, you could use the following query string:

“SELECT * WHERE A > 10”

Where the “*” means “all columns”, and “A” is the column to search for values greater than 10.

The [headers] parameter is optional, and allows you to specify the number of header rows included in the data range. You can simply omit it or set it to -1 to let Google Sheets automatically detect the number of header rows.

I personally find using the [headers] parameter to be a bit of a pain, so I generally select a range of cells that omits any header rows, and leave that option out.

QUERY Function Example

Let’s say I have a spreadsheet with a list of food and alcohol expenses that spans several years:

In the above example, I’ve got dates in column B, store names in column C, dollar amounts in column D, and I want to only show stores from column C where the year in column B is 2023.

To do this, in a separate cell somewhere else in the sheet, I can use the following basic query:

=QUERY(B5:C,"SELECT C WHERE YEAR(B)=2023")

In the above query, I’ve used columns B and C as my range, as those are the columns containing the data I want to query, AND the data I want to return.

Using that query, I get the following results (note that this is just a small snippet):

Maxi
Maxi
Maxi
Maxi
SAQ
Maxi
Maxi
Boulangerie Marche de L'Ouest
Bulk Barn
Maxi
SAQ

QUERY Function Example with Multiple Conditions

Alright, let’s say instead I want to filter the data to only show expenses from 2023, and where the store name is Maxi. I can do this by expanding the range to include column D, and adding an “AND” clause to my query, as such:

=QUERY(B5:D,"SELECT D WHERE YEAR(B)=2023 AND C='Maxi'")

Using that query, I get the following results (again, this is just a small snippet):

$188.70 
$177.08 
$151.15 
$11.20 
$170.40 
$88.49 
$128.75 
$99.54 
$155.64 
$18.93 
$97.14 

Note: Unlike in SQL, with Google’s QUERY() function, the string values in the WHERE clauses are case-sensitive.

For example, had I used “c='maxi'” or “c='MAXI'” in the above query, I would have had no results. So be sure to use the correct case when filtering by string values.

These were just two very basic queries to give you an idea of how the QUERY() function works.

There’s a lot more you can do with the QUERY function, including returning multiple columns, sorting the returned data, and more, but that’s a bit beyond the scope of this post.

The full documentation can be found in Google’s official docs.

The FILTER Function

The FILTER function is another way to filter data in Google Spreadsheets. It’s quite a bit simpler than the QUERY function as you don’t need to know SQL syntax to use it, but it’s also a bit more limited in what you can do with it.

Despite it’s limitations though, it’s still a very useful function, and one I use much more often than the QUERY function, as it generally suffices.

FILTER Function Syntax

The syntax for the FILTER() function is as follows:

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

The range parameter is the range of cells you want to return. As with the QUERY() function, this can be a range of cells, or an entire sheet.

The condition1, condition2, etc. parameters are the conditions you want to filter the data by. You can have as many conditions as you want, but you need to have at least one.

Each condition is a range of cells, followed by a comparison operator, followed by a value to compare the cells to. In the QUERY() function, this would be the WHERE clause.

If we use the same sheet as above where I’ve been tracking food and alcohol expenses:

And as in the first example above where I wanted to only show store names from column C where the year in column B is 2023, I can use the following basic filter:

=FILTER(C:C,YEAR(B:B)=2023)

I’ve included only a single condition in the above example, like in the QUERY() function example, but here I need to provide the entire range of cells (“YEAR(B:B)”) in spreadsheet notation, not just a single column (“YEAR(B)”) like in the QUERY() function’s WHERE clause in the 1st QUERY example.

Using the above filter, I get the exact same results as I would have using the QUERY() function:

Maxi
Maxi
Maxi
Maxi
SAQ
Maxi
Maxi
Boulangerie Marche de L'Ouest
Bulk Barn
Maxi
SAQ

FILTER Function Example with Multiple Conditions

Again, using the QUERY() example above where I want to filter the data to only show expenses from 2023, and where the store name is Maxi, I would do this by modifying the range of cells to return, and adding a second condition to my filter:

=FILTER(D:D,YEAR(B:B)=2023,C:C="Maxi")

Once again, I get the exact same results as I would have using the QUERY() function:

$188.70 
$177.08 
$151.15 
$11.20 
$170.40 
$88.49 
$128.75 
$99.54 
$155.64 
$18.93 
$97.14 

Note: The filter parameters here are not case-sensitive. So “C:C="maxi"”, “C:C="MAXI"”, or any other mix of case would work here.

So while the FILTER() function won’t allow you to do everything the QUERY() function will, like sorting or manipulating the resulting data, it’s still a very useful tool for filtering data in Google Spreadsheets.

You can of course wrap the output of the FILTER() function in a SORT() function for sorting, a SUM() to sum all the numbers, or do other manipulations on the data, so you’re not limited to just filtering. The difference here is that you can’t do those manipulations within the FILTER() function itself, and need to wrap it in another function.

Final Thoughts

So those are the two ways I would typically filter data in a Google Spreadsheet.

Generally I’d be using the FILTER() function if I just need to grab the data, and then wrap it in another function like SUM() or AVERAGE() to get the total or average of the filtered data.

For anything complex, I might opt to use the QUERY() function instead. The fact that it uses SQL-syntax is a great bonus if you’re familiar with SQL, and makes it an easy transition from querying a database to querying a spreadsheet.