Mastering QUERY Function in Google Sheets: Complete Guide with Examples & Interview Questions

The QUERY function in Google Sheets is one of the most powerful and versatile tools available. It allows you to perform SQL-like data manipulations—filtering, sorting, aggregating, and grouping—on your spreadsheet data.

📌 QUERY Function Syntax

QUERY(data, query, [headers])

🔹 Arguments:

data: The range of cells that you want to query.
query: A string written in a pseudo-SQL format.
headers (optional): The number of header rows at the top of your data (default is 1).

🧠 Why Use QUERY?

It combines the power of multiple functions like FILTER, SORT, VLOOKUP, SUMIF, UNIQUE, and even PIVOT TABLES—all in one.

✅ Basic Examples

Assume we have the following data in A1:D6:

Name	Age	Department	Salary
John	25	Sales	30000
Alice	30	HR	35000
Bob	24	Sales	28000
Carol	29	Marketing	40000
Dave	35	HR	38000

🔹 1. Select All Rows

=QUERY(A1:D6, "SELECT *", 1)

🔸 Returns the full table.

🔹 2. Select Specific Columns

=QUERY(A1:D6, "SELECT A, C", 1)

🔸 Returns only Name and Department columns.

🔹 3. Filtering Rows (WHERE Clause)

=QUERY(A1:D6, "SELECT A, D WHERE C = 'Sales'", 1)

🔸 Shows Name and Salary of employees in Sales department.

🔹 4. Using Comparison Operators

=QUERY(A1:D6, "SELECT A, B WHERE D > 30000", 1)

🔸 Returns Name and Age of employees earning more than 30,000.

🔹 5. Sorting (ORDER BY)

=QUERY(A1:D6, "SELECT A, D ORDER BY D DESC", 1)

🔸 Returns Name and Salary sorted by Salary in descending order.

🔹 6. Grouping and Aggregating (GROUP BY)

=QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C", 1)

🔸 Calculates average salary per department.

🔹 7. Labeling Columns

=QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C LABEL AVG(D) 'Average Salary'", 1)

🔸 Adds a custom label to the aggregated column.

🔹 8. Limit Results

=QUERY(A1:D6, "SELECT * LIMIT 3", 1)

🔸 Returns only the first 3 rows.

🔹 9. Combining WHERE and ORDER BY

=QUERY(A1:D6, "SELECT A, D WHERE D > 30000 ORDER BY D DESC", 1)

🔸 Filters employees with salary > 30,000 and sorts them in descending order.

🔹 10. Dynamic Query with Cell Reference

=QUERY(A1:D6, "SELECT A, D WHERE D > "&E1, 1)

🔸 Assuming cell E1 has the value 30000, this will filter dynamically.

⚠️ Notes:

Text values in queries must be enclosed in single quotes (‘ ‘).
Numbers and cell references can be added directly.
QUERY is case-insensitive by default.

🎯 Common Use Cases

Creating dashboards
Creating dynamic reports
Filtering datasets based on dropdown selections
Summarizing large data tables
Converting flat data into summarized views like pivot tables

📘 Real-Life Example:

Imagine a school with student records:

Student	Class	Subject	Marks
Rahul	10	Math	85
Sneha	10	Science	90
Aman	11	Math	78
Priya	10	Math	92

To find average marks in each subject for class 10:

=QUERY(A1:D5, "SELECT C, AVG(D) WHERE B = 10 GROUP BY C", 1)

🔸 Returns Math and Science with their average marks for class 10 students.

💼 Top 10 Interview Questions on Google Sheets QUERY Function

Q: What is the QUERY function in Google Sheets?
A: It allows you to use SQL-like queries to filter, sort, group, and summarize data.
Q: How do you filter records using a text value in QUERY?
A: Use WHERE column = 'Text', e.g., WHERE C = 'Sales'.
Q: How can you sort data using QUERY?
A: Use ORDER BY clause: ORDER BY column [ASC|DESC].
Q: What does GROUP BY do in QUERY?
A: It aggregates values (e.g., SUM, AVG) based on unique groups in a column.
Q: How do you rename column headers in the QUERY result?
A: Use the LABEL clause: LABEL AVG(D) 'Average Salary'.
Q: What’s the difference between SELECT * and SELECT A, B?
A: SELECT * selects all columns; A, B selects only specific columns.
Q: How can you use a cell reference in a QUERY?
A: Concatenate it: "SELECT A WHERE B > "&E1
Q: Can you use OR and AND in QUERY filters?
A: Yes. Example: WHERE B > 25 AND C = 'HR'
Q: What happens if you omit the headers parameter?
A: QUERY assumes the first row is the header by default (1).
Q: How is QUERY different from FILTER function?
A: FILTER is simpler and only filters data. QUERY is more powerful with sorting, aggregation, grouping, and SQL-like operations.