The QUERY function is arguably the most powerful tool in Google Sheets. It allows you to use SQL-like code (Google Visualization API Query Language) to filter, sort, and manipulate data from a large dataset with a single formula. If you find the FILTER function too limited, QUERY is your next step toward automation mastery.

Step 1: Understand the Basic Syntax

The QUERY function follows a specific structure: =QUERY(data, query, [headers]). The data is the range of cells you want to analyze, the query is the command (written in double quotes), and headers is an optional number indicating how many rows contain titles.

Step 2: Select Specific Columns

Instead of pulling the entire dataset, you can choose exactly which columns to display. Use the SELECT clause followed by the column letters. For example, to pull only names (Column A) and salaries (Column C), use: =QUERY(A:E, "SELECT A, C"). If you want to select everything, use SELECT *.

Step 3: Filter Data with the WHERE Clause

To narrow down your results based on specific criteria, use the WHERE clause. This is the equivalent of a 'Filter' in Excel. For example, to see only employees in the 'Sales' department (Column B), your formula would be: =QUERY(A:E, "SELECT A, C WHERE B = 'Sales'"). Note that text values must be wrapped in single quotes.

Step 4: Sort Results with ORDER BY

You can automatically sort your results without touching the 'Sort' menu. Add the ORDER BY clause to the end of your string. To sort salaries (Column C) from highest to lowest, use: =QUERY(A:E, "SELECT A, C WHERE B = 'Sales' ORDER BY C DESC"). Use ASC for ascending and DESC for descending order.

Step 5: Perform Calculations with GROUP BY

The QUERY function can act like a Pivot Table. You can use aggregation functions like SUM, AVG, COUNT, MIN, or MAX. For instance, to see the total salary per department, use: =QUERY(A:E, "SELECT B, SUM(C) GROUP BY B"). Whenever you use an aggregation function, you must GROUP BY the non-aggregated columns.

Step 6: Use LABEL to Rename Headers

By default, QUERY creates headers like 'sum Salary'. To make your report professional, use the LABEL clause. For example: =QUERY(A:E, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Payroll'"). This keeps your data clean and ready for presentation.

Pro Tip: Handling IMPORTRANGE Data

If you are querying data from another spreadsheet using IMPORTRANGE, you cannot use column letters (A, B, C). Instead, you must use Col1, Col2, Col3 notation. Example: =QUERY(IMPORTRANGE("URL", "Sheet1!A:Z"), "SELECT Col1, Col3 WHERE Col2 > 100").

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