This guide covers everything you need to know about the Google Sheets QUERY function, including its definition, syntax, use cases, and how to use it.
What is the QUERY Function? How Does It Work?
The QUERY function in Google Sheets returns data by running a query on a specific range of cells. This query is written in Google Visualization API Query Language, a language designed specifically for managing and manipulating data. This language can perform a wide range of tasks. For instance, you can use the QUERY function to return rows that match a specific condition (using Select and Where clauses), aggregate certain values across rows (using Select and Group by clauses), or transform distinct column values into new ones (using Pivot).
Additionally, the QUERY function can order data by a specific key (using Order by) and handle multiple header rows in the input range. This is done through the ‘headers’ argument, which specifies the number of header rows at the top of the data.
It’s worth noting that the QUERY function can accept either “Col” notation or “A, B” notation. This flexibility allows users to express complex data manipulations in a more intuitive and straightforward way.
The QUERY function is extremely valuable for anyone working with large datasets, as it allows you to quickly extract relevant information, make calculations, and manipulate data to suit your needs. Once mastered, it’s a powerful feature that can greatly enhance your ability to work effectively with data in Google Sheets.
QUERY Syntax
The syntax and arguments for the function are as follows:
QUERY(data, query, [headers])
Where:
- data: This is the range of cells on which the query will be executed. It is required to specify this range for the QUERY function to work. This could be a specific set of cells like A2:E6 or an entire column like A:A.
- query: This is the actual query that will be performed. It is written in the Google Visualization API Query Language. This query can include commands such as “select”, “where”, “group by”, “pivot”, “order by” and others. It is essential for this argument to be written in quotes (“”).
- headers: This is an optional argument. It specifies the number of header rows at the top of the data range. If it is omitted or set to -1, Google Sheets will guess the number based on the content of the data. If you have multiple header rows, you can specify the number here to ensure accurate results.
Here are some important notes about the syntax and arguments:
- The QUERY function can accept either “Col” notation or “A, B” notation. This means you can refer to columns either by their letter (like A, B, C) or by the “Col” notation (like Col1, Col2, Col3) in your query.
- The queries are case-sensitive. For example, “select” and “SELECT” are not the same in the Google Visualization API Query Language.
- The query argument must always be written in quotes. If you are using a cell reference for your query, it should still be written in quotes.
- If your data range includes multiple rows of headers, you should specify the number in the headers argument to ensure accurate results.
- The headers argument is optional. If it’s not provided, Google Sheets will guess the number based on the contents of the data. However, for more complex data sets, it is recommended to specify this argument.
Examples of How to Use the QUERY Function
Here are some practical examples of how to use the QUERY function in Google Sheets:
Example #1: Selecting Specific Columns
Let’s say you have a spreadsheet with a list of employees, their departments, and their salaries. You only want to see the names and salaries. The QUERY function can help with this. Assuming the data is in columns A, B, and C, you can use the following formula:
=QUERY(A:C, “SELECT A, C”)
This will return a new table with only the employee names (column A) and their salaries (column C).
Example #2: Filtering Data
Using the same employee data, suppose you want to see only those employees who earn more than $50,000. You can use the QUERY function to filter the data as follows:
=QUERY(A:C, “SELECT * WHERE C > 50000”)
This will return a table with only the rows where the salary (column C) is greater than $50,000.
Example #3: Sorting Data
The QUERY function can also sort your data. If you want to see the employees sorted by salary in descending order, you can use the following formula:
=QUERY(A:C, “SELECT * ORDER BY C DESC”)
This will return a table with all the rows sorted by the salary (column C) in descending order.
Example #4: Grouping Data
The QUERY function can also group your data. If you want to know the total salary paid per department, you can use the following formula:
=QUERY(A:C, “SELECT B, SUM(C) GROUP BY B”)
This will return a table with each department (column B) and the total salary for that department.
Example #5: Limiting Results
If you only want to see the top 5 earners in the company, you can use the QUERY function to limit the results:
=QUERY(A:C, “SELECT * ORDER BY C DESC LIMIT 5”)
This will return a table with only the top 5 rows, sorted by the salary (column C) in descending order.
Remember, the QUERY function is very powerful and can be used to manipulate your data in many ways. These examples are just the tip of the iceberg.
Why Is QUERY Not Working? Troubleshooting Common Errors
If you’re using the QUERY function in Google Sheets and running into problems, you’re not alone. This powerful function can sometimes throw errors that can be confusing. Here, we’ll cover some of the most common errors, their causes, and solutions.
#VALUE! Error
Cause: This error usually occurs when the function can’t interpret the data in the cell it refers to. This could be because the cell contains text instead of a number or because the cell is empty.
Solution: Check the cell that the QUERY function is referring to. Ensure that it contains the type of data the function is expecting. If the cell is supposed to contain a number, ensure it does and that there’s no additional text or symbols.
#REF! Error
Cause: The #REF! error typically indicates that the QUERY function refers to a cell or range of cells that doesn’t exist. This could happen if you deleted a row or column that the function was referring to.
Solution: Review your function to ensure all cell references are correct. If you’ve deleted any rows or columns, remember to update your function to refer to the correct cells.
#N/A Error
Cause: The #N/A error usually appears when the QUERY function can’t find the data it’s looking for. This could happen if the data has been moved, deleted, or the function looks in the wrong place.
Solution: Double-check the range of cells your function is querying. Make sure the data is still there and in the correct place. If your data has moved or been deleted, you need to update the function to point to the correct location.
#ERROR! Error
Cause: The #ERROR! error is a generic error that can mean a lot of different things. It usually shows up when your QUERY function has a syntax error.
Solution: Review your QUERY function carefully for any mistakes in syntax. Ensure you’ve used the correct quotation marks, parentheses, and commas. If you’re having trouble, try breaking the function down into smaller parts and testing each one individually.
#DIV/0! Error
Cause: The #DIV/0! error shows up when the QUERY function attempts to divide by zero, which is mathematically undefined.
Solution: Check your function to see if there’s a division operation. If there is, make sure the denominator isn’t zero. If the denominator is a cell reference, ensure the cell contains a number other than zero.
#NUM! Error
Cause: The #NUM! error occurs when the QUERY function deals with numbers that are too big or too small.
Solution: If you’re dealing with very large or small numbers, try breaking them down into smaller parts or using scientific notation. Google Sheets can handle numbers between 10^308 and 10^-308.
Using QUERY With Other Google Sheets Functions
Let’s look at some examples of how QUERY can be used with other functions to get the most out of your data analysis.
With SUM
Usage: The SUM function adds up all the numbers in a range of cells. You can use QUERY with SUM to add up all the numbers that meet certain conditions.
Example: Assume you have a sheet with sales data and want to sum the sales for a specific product. You can use the QUERY function to get all the rows with that product and then use the SUM function to add up the sales.
=SUM(QUERY(A2:B10, “select B where A=’Product X'”))
In this example, A2:B10 is the range, “select B where A=’Product X'” is the query, and the SUM function adds up all the sales for ‘Product X’.
With COUNT
Usage: The COUNT function counts the number of cells in a range that contain numbers. You can use QUERY with COUNT to count the cells that meet certain conditions.
Example: Suppose you have a list of employees and want to count how many of them work in a specific department. You could use the QUERY function to get all the rows with that department and then use the COUNT function to count the employees.
=COUNT(QUERY(A2:B10, “select A where B=’Marketing'”))
In this example, A2:B10 is the range, “select A where B=’Marketing'” is the query, and the COUNT function counts the number of employees in the ‘Marketing’ department.
With AVERAGE
Usage: The AVERAGE function calculates the average of the numbers in a range of cells. You can use QUERY with AVERAGE to compute the average of the numbers that meet certain conditions.
Example: Assume you have a sheet with student grades and you want to compute the average grade for a specific subject. You could use the QUERY function to get all the rows with that subject and then use the AVERAGE function to compute the average grade.
=AVERAGE(QUERY(A2:B10, “select B where A=’Math'”))
In this example, A2:B10 is the range, “select B where A=’Math'” is the query, and the AVERAGE function computes the average grade for ‘Math’.
For more details on the QUERY function, check out the official documentation at the Google Docs Editors Help Center.
