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 data from a large dataset into a new, organized view. If you have ever felt limited by simple filters, the QUERY function is the solution you need.

Step 1: Understand the QUERY Syntax

Before diving into the steps, you must understand the basic structure of the formula:

=QUERY(data, query, [headers])

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

Step 2: Basic Data Selection

To pull all data from one sheet to another, or to select specific columns, use the SELECT clause. For example, if you only want columns A and C from a dataset, your formula would look like this:

=QUERY(A1:E100, "SELECT A, C")

This tells Google Sheets to ignore columns B, D, and E and only display the data from A and C.

Step 3: Filtering Data with the WHERE Clause

The WHERE clause is used to filter data based on specific conditions. For instance, if you have a list of sales and only want to see rows where the amount (Column B) is greater than 500, use:

=QUERY(A1:E100, "SELECT * WHERE B > 500")

Note: The * symbol means "select all columns." If you are filtering by text, the text must be in single quotes (e.g., WHERE C = 'Completed').

Step 4: Sorting Results with ORDER BY

To organize your filtered data, use ORDER BY. You can sort in ascending (ASC) or descending (DESC) order. To show your highest sales first, the formula would be:

=QUERY(A1:E100, "SELECT * WHERE B > 500 ORDER BY B DESC")

Step 5: Limiting the Output

If you only want to see the Top 5 results, add the LIMIT clause at the end of your query string:

=QUERY(A1:E100, "SELECT * ORDER BY B DESC LIMIT 5")

Step 6: Referencing Cell Values in a Query

A common mistake is trying to type a cell reference directly inside the quotes. To make a query dynamic based on a value in cell G1, you must use concatenation:

=QUERY(A1:E100, "SELECT * WHERE C = '"&G1&"'")

For numbers, the syntax is slightly different: "SELECT * WHERE B > "&G1&"". This allows you to change the filter criteria without editing the formula itself.

Pro Tip: Combining Multiple Conditions

You can use AND and OR to create complex filters. For example, to find sales over 500 that are also marked as 'Shipped':

=QUERY(A1:E100, "SELECT * WHERE B > 500 AND C = 'Shipped'")

Mastering the QUERY function will significantly reduce the time you spend manually filtering and copying data in Google Sheets, making your workflows much more efficient.

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