Transform with QUERY()

We’ve mastered lookup functions like VLOOKUP() and XLOOKUP(), which are incredibly useful for finding specific values in our spreadsheets. For example, we might use VLOOKUP() to find a single employee’s salary from their ID number. But what if our goal isn’t just to find one piece of data, but to ask bigger questions about the entire dataset? For instance:

• Show me a list of all employees in the Marketing department who were hired in the last year.

• What is the total salary cost for each department, sorted from highest to lowest?

• Which five employees have the highest salary, and what are their job titles?

That’s where the QUERY() function comes in.

QUERY() is one of Google Sheets’ most powerful functions; it lets us perform complex data analysis with a single line of code, almost like working with a database. Instead of applying multiple filters or chaining formulas, we can write one clean QUERY() to extract exactly what we need. It’s especially helpful for large datasets, where manual methods can be slow and error-prone.

The QUERY() function

QUERY() is a function that processes a range of data based on a query string, allowing us to perform operations like selecting, filtering, sorting, and summarizing data. A query string is a command, written in a language similar to SQLSQL is a language used to store, manage, and retrieve data from relational databases., that tells the QUERY() function exactly what to do with the data. It’s incredibly transformative, because it allows us to do ...