The QUERY function is arguably the most powerful tool in Google Sheets. It allows you to use SQL-like commands to filter, sort, and manipulate your data with a single formula. Instead of using complex nested filters or multiple helper columns, the QUERY function can handle everything in one place.

In this guide, we will break down the syntax and show you exactly how to master this function to boost your productivity.

Step 1: Understand the QUERY Function Syntax

Before writing the code, you need to understand the structure of the formula. The syntax for the QUERY function is:

=QUERY(data, query, [headers])

• data: The range of cells you want to analyze (e.g., A1:E100).
• query: The string containing your instructions (written in Google Visualization API Query Language). This must be enclosed in quotation marks.
• headers: (Optional) The number of header rows at the top of your data.

Step 2: Performing a Basic Select Query

The most common use of QUERY is to pull specific columns from a large dataset. If you have a sheet with columns A through F but only need to see Name (A) and Salary (F), use the following:

=QUERY(A:F, "SELECT A, F")

This tells Google Sheets to ignore the other columns and only display the data from A and F.

Step 3: Filtering Data with the WHERE Clause

To find specific information, such as employees in a "Sales" department (Column C), you use the WHERE clause. This acts as a powerful filter.

=QUERY(A:F, "SELECT A, C, F WHERE C = 'Sales'")

Note that text values inside the query must be wrapped in single quotes ('Sales').

Step 4: Sorting Data with ORDER BY

You can automatically sort your results without touching the filter icons. If you want to see the highest salaries at the top, use ORDER BY combined with DESC (descending):

=QUERY(A:F, "SELECT A, F ORDER BY F DESC")

To sort in ascending order, you can use ASC or simply leave it blank, as ascending is the default.

Step 5: Using Mathematical Operators and Limits

The QUERY function can also handle numbers and limit the number of results returned. For example, to find the top 5 employees earning more than $50,000:

=QUERY(A:F, "SELECT A, F WHERE F > 50000 ORDER BY F DESC LIMIT 5")

Step 6: Referencing Cell Values in a Query

To make your QUERY dynamic, you might want it to pull criteria from a specific cell (like cell H1) rather than typing it into the formula. This requires a specific syntax to break the string:

=QUERY(A:F, "SELECT A, C, F WHERE C = '"&H1&"'")

The '"&H1&"' sequence allows the formula to treat the content of cell H1 as part of the query string.

Troubleshooting Common QUERY Errors

• #VALUE! Error: This usually happens if your data range contains mixed data types (e.g., text and numbers in the same column). QUERY works best when each column has a consistent data type.
• #N/A Error: This occurs when the WHERE clause finds no matches in your data.
• Formula Parse Error: Double-check your quotation marks. The entire query must be in double quotes, and text criteria inside must be in single quotes.

💡 Pro Tip: Keep your software updated to avoid these issues in the future.