If you’ve ever written SQL queries to get data out of a database, then you’ll recognize the QUERY function. If you don’t have database experience, the QUERY function is still very easy to learn.
What Is the QUERY Function?
The function has three main parameters:
=QUERY(data, query, headers)
These parameters are fairly straightforward.
Data: The range of cells that contain the source dataQuery: A search statement describing how to extract what you want from the source dataHeaders: An optional argument that lets you combine multiple headers in the source range into a single header in the destination sheet
The flexibility and power of the QUERY function comes from the Query argument, as you’ll see below.
How to Create a Simple QUERY Formula
The QUERY formula is especially useful when you have a very large data set from which you need to extract and filter data.
The following examples use U.S. SAT high school performance statistics. In this first example, you’ll learn how to write a simple QUERY formula that returns all high schools and their data where “New York” is in the name of the school.
Create a new sheet for placing the query results. In the upper left cell type =Query(. When you do this, you’ll see a pop-up window with require arguments, an example, and helpful information about the function. Next, assuming you have the source data in Sheet1, fill in the function as follows: =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B LIKE ‘%New York%’”). This formula includes the following arguments: Range of Cells: The range of data in A1 to F460 in Sheet1SELECT Statement: A SELECT statement that calls for any data in columns B, C, D, E, and F where column B contains text that has the word “New York” in it. The “%” character is a wildcard that you can use to search for parts of strings or numbers in any data set. Leaving “%” off the front of the string would return any school name that starts with the text “New York”. If you wanted to find the name of an exact school from the list, you could type the query: =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”). Using the = operator finds an exact match and can be used to find matching text or numbers in any column.
Because the the Google Sheets QUERY function is very easy to understand and use, you can pull any data out of any large data set using simple query statements like the ones above.
=Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B LIKE ‘%New York%’”).
This formula includes the following arguments:
Range of Cells: The range of data in A1 to F460 in Sheet1SELECT Statement: A SELECT statement that calls for any data in columns B, C, D, E, and F where column B contains text that has the word “New York” in it.
=Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”).
Using the = operator finds an exact match and can be used to find matching text or numbers in any column.
Use the QUERY Function With a Comparison Operator
Comparison operators let you use the QUERY function to filter out data that doesn’t meet a condition.
You have access to all of the following operators in a QUERY function:
=: Values match the search value<: Values are less than the search value>: Values are greater than the search value<=: Values are less than or equal to the search value>=: Values are greater than or equal to the search value<> and !=: Search value and source values are not equal
Using the same SAT example data set above, let’s take a look at how to see which schools had an average mathematics mean above 500 points.
In the upper left cell of a blank sheet, fill in the QUERY function as follows: =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE E > 500”) This formula calls for any data where column E contains a value that’s greater than 500. You can also include logical operators like AND and OR to search for multiple conditions. For example, to pull scores only for schools with over 600 test takers and a critical reading mean between 400 and 600, you would type the following QUERY function: =Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600”) Comparison and logical operators provide you with many different ways to pull data from a source spreadsheet. They let you filter out important pieces of information from even very large data sets.
Advanced Uses of QUERY Function
There are a few other features you can add to the QUERY function with some additional commands. These commands let you aggregate values, count values, order data, and find maximum values.
=Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE E > 500”)
This formula calls for any data where column E contains a value that’s greater than 500.
=Query(Sheet1!A1:F460,“SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600”)
Using GROUP in a QUERY function allows you to aggregate values in multiple rows. For example, you can average test grades for each student using the GROUP function. To do this, type: =Query(Sheet1!A1:B24,“SELECT A, AVG(B) GROUP BY A”) Using COUNT in a QUERY function, you could count the number of schools with a writing mean score over 500 using the following QUERY function: =QUERY(Sheet1!A2:F460,“SELECT B, COUNT (F) GROUP BY B”) Using ORDER BY in a QUERY function, you can find schools with maximum math mean scores and orders the list by those scores. =QUERY(Sheet1!A2:F460,“SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)”)
=Query(Sheet1!A1:B24,“SELECT A, AVG(B) GROUP BY A”)
=QUERY(Sheet1!A2:F460,“SELECT B, COUNT (F) GROUP BY B”)
=QUERY(Sheet1!A2:F460,“SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)”)
