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

—

by

Himanshu
in

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:

  1. data: The range of cells that you want to query.
  2. query: A string written in a pseudo-SQL format.
  3. 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:

NameAgeDepartmentSalary
John25Sales30000
Alice30HR35000
Bob24Sales28000
Carol29Marketing40000
Dave35HR38000

🔹 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:

StudentClassSubjectMarks
Rahul10Math85
Sneha10Science90
Aman11Math78
Priya10Math92

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

  1. 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.
  2. Q: How do you filter records using a text value in QUERY?
    A: Use WHERE column = 'Text', e.g., WHERE C = 'Sales'.
  3. Q: How can you sort data using QUERY?
    A: Use ORDER BY clause: ORDER BY column [ASC|DESC].
  4. Q: What does GROUP BY do in QUERY?
    A: It aggregates values (e.g., SUM, AVG) based on unique groups in a column.
  5. Q: How do you rename column headers in the QUERY result?
    A: Use the LABEL clause: LABEL AVG(D) 'Average Salary'.
  6. Q: What’s the difference between SELECT * and SELECT A, B?
    A: SELECT * selects all columns; A, B selects only specific columns.
  7. Q: How can you use a cell reference in a QUERY?
    A: Concatenate it: "SELECT A WHERE B > "&E1
  8. Q: Can you use OR and AND in QUERY filters?
    A: Yes. Example: WHERE B > 25 AND C = 'HR'
  9. Q: What happens if you omit the headers parameter?
    A: QUERY assumes the first row is the header by default (1).
  10. 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.

Featured products