For our example, we have the small table below stored in Google Sheets:
We want to list all Type A names whose values are more than 15. The column containing the type is the 2nd column while the column containing the values is the 3rd column. For the first condition we can write that as
B = ‘A’
The formula means that you check the 2nd column (B) for cells that contain the value “A” (enclosed in single quotes). This is how you check whether a cell has a specific strings in Google Sheets.
While the second condition we can write as
C>15
We can combine them using the AND command to indicate that we need both conditions fulfilled:
B=’A’ AND C>15
Finally, we add the SELECT and WHERE commands to specify the columns of the range we query. For our example, we include all the columns. Thus,
SELECT * WHERE B=’A’ AND C>15
The syntax of the QUERY function is
=QUERY(data,query)
Where data is the range of the table you want to process and query is the command you want to use to process the data. The query must be enclosed in double quotes.
In our example, the range of the table we query is A1:C6. Noting the query as
SELECT * WHERE B=’A’ AND C>15
The formula becomes
=QUERY(A1:C6,"SELECT * WHERE C>15 AND B='A'")
We place it in cell E1 in the same sheet.
Press Enter once you enter the formula in the cell. Depending on the size of the table, the loading of the output may take some time.
Quite straightforward, right? As long as you know how to make queries, it is easy to construct the formula.
SELECT column1, column2, …
Where column1 and column2 are the columns of the table you want to output, written in letters. For example, if you have a 5-column table and you want to output the 2nd and 3rd column, the query is
SELECT B, C
If you want to use QUERY function to filter entries using a condition applied to a column, then the query is
SELECT * where condition
Where the condition uses the column letter to identify the column it is applied. The asterisk means all the columns are selected by the query. This means that the output will display all columns of the entries filtered. For example, we want to filter the table by only listing all entries whose values in the third column is more than 15, then the query becomes
SELECT * where C>15
Almost the same as the query above, but you can use AND and OR. For example we have a 3-column table with the following info each column:
Column A: Name
Column B: Type
Column C: Value
We want to get all entries whose values in the third column is more than 15 and the type is type A. The resulting query is
SELECT * where C>15 AND B=’A’
For more information about the commands you can use, check the FAQ section below.
Below is a table of a list of commands you can use with the QUERY function. To learn more about each function, click the specific command.
Selects the column/s to analyze. Examples:
To select the first two columns in the range: SELECT A,B
To select all columns from the source: SELECT *
Returns rows that match the conditions defined after selecting the columns.
AND: Returns rows where both conditions are met. Syntax: Condition1 AND condition2
OR returns rows that match at least one declared condition. Syntax: Condition1 OR condition2
NOT returns rows that do not match the given condition or conditions. Syntax: NOT Condition1
Arranges the rows using the values stored in a selected column. Example:
Arrange the rows by the values in the third column: ORDER BY C
Aggregates rows by the values in the specified column. Example:
To aggregate the rows using the values in column B: GROUP BY B
Limits the results to the first N rows. Example:
Display only the first 10 entries LIMIT 10
Skips the first N rows in the output. Example:
Display the rows, skipping the first 10 entries: OFFSET 10
Finds the minimum or maximum value in the selected column. Works as a function, with the column enclosed in parenthesis. Example:
Display the maximum and minimum value in the third column: MAX(C), MIN(C);
The values are displayed into separate cells
COUNT(): Calculates the total number of rows with values in the selected column
AVG(): Calculates the average value of the selected column
SUM(): Calculates the total value of the selected column.
Example:
Calculate the total number of entries, the average value, and the sum of the values in column A
COUNT(A), AVG(A), SUM(A)
The values are displayed into separate cells
Lists rows whose cell in the selected column contains the string. The string can be described using wildcards. Example:
List rows whose first column contains a given string
WHERE A LIKE ‘string’
Lists rows whose cell in the selected column either starts with or ends with the specified string, respectively. Example:
List rows whose first column starts with a given string
WHERE A STARTS WITH ‘string’
Lists rows whose cell in the selected column has the exact string specified. Example:
List rows whose first column has the exact given string
WHERE A MATCHES ‘string’
Lists rows whose cell in the selected column contains the string specified as part of its value. Example:
List rows whose first column contains a given string
WHERE A CONTAINS ‘string’
Inserts a header row containing the label/s for the selected columns. Example:
Add header label string to the first column
LABEL A ‘string’
Used to convert string input to the command to date and datetime format used in Google Sheets. Example:
List all entries whose date stored in the first column is date:
WHERE A CONTAINS DATE ‘date’
Used to transform distinct values in selected columns into new columns, behaves much like Pivot Table in Google Sheets. Usually combined with GROUP BY. Example:
List the sum of the values stored at the second column per unique entry in the first column:
SELECT A, SUM(B) GROUP BY A
Sets the format of the cells in the selected row. Only modifies the output. Example:
Formats the dates stored in the first column to yyyy-mm-dd:
FORMAT A ‘YYYY-MM-DD’
Absolutely! For example, we can combine QUERY with IMPORTRANGE, a powerful function that can be used to consolidate data from different spreadsheets to a single one. The formula is
=QUERY(IMPORTRANGE(sheet_url,data_range), query)
Where
Sheet_url is the URL of the Google Sheets spreadsheet you want to import and query through QUERY function;
Data_range is the range of the table stored in the sheet_url. This includes the sheet name; and
query is the command you want to use to analyze the table.
All these three inputs must be enclosed in double quotes.
For example, we have the following table stored in Spreadsheet A, on the sheet named Sheet2:
We want to query only the entries where the total amount is more than 14. The formula becomes:
=QUERY(IMPORTRANGE(sheet_url,”Sheet2”), "SELECT * WHERE D>14")
You can combine two or more sources to the same QUERY function. Here is the formula, for the case of three ranges:
=QUERY({IMPORTRANGE(sheet1_url,data_range1),IMPORTRANGE(sheet2_url,data_range2),IMPORTRANGE(sheet3_url,data_range3)}, query)
Where
Sheet1_url, sheet2_url, and sheet3_url are the URLs of the Google Sheets spreadsheets you want to import and query through QUERY function;
Data_range1, data_range2, and data_range3 are the ranges of the tables stored in the Sheet1_url, sheet2_url, and sheet3_url, respectively. This includes the sheet name; and
query is the command you want to use to query the combined tables.
You can also use it to import several sheets from the same spreadsheet URL by specifying the same URL for each time. Learn more about these here.
