QUERY Function in Google Sheets

When you work with large data in Google Sheets, using formulas like VLOOKUP or FILTER sometimes feels slow or complex. That’s where the QUERY function comes in. It allows you to run SQL-like commands (like select, where, order by) directly inside Google Sheets.

It’s like having a mini-database inside your spreadsheet.

In this article, we’ll understand how the QUERY function works, see practical MIS use cases, and learn common fixes.

Table of Contents

What is QUERY Function in Google Sheets?

The QUERY function lets you filter, sort, and analyze data using a text-based query, similar to SQL. Instead of writing multiple formulas, you can write one QUERY to do it all.

Syntax

=QUERY(data, query, [headers])

data → The range of your data (example: A1:D100)
query → The SQL-like command written inside quotes (example: “select A, B where C > 5000”)
headers → Optional; tells Sheets how many header rows your data has (usually 1)

Example 1: Select Specific Columns

If you have data in A:D (Name, Department, Sales, Date) but only want Name and Sales:

=QUERY(A1:D100, "select A, C", 1)

👉 Returns only Name and Sales columns.

Example 2: Filter with WHERE

Show employees with Sales greater than 5000:

=QUERY(A1:D100, "select A, C where C > 5000", 1)

Example 3: Sorting Data

Sort employees by Sales (highest first):

=QUERY(A1:D100, "select A, C order by C desc", 1)

Example 4: Using LABEL to Rename Columns

=QUERY(A1:D100, "select A, C label A 'Employee Name', C 'Total Sales'", 1)

👉 Renames column headers in the result.

Example 5: Using QUERY with Dates

If you want data only after 1st Jan 2024:

=QUERY(A1:D100, "select A, C, D where D > date '2024-01-01'", 1)

⚠️ Note: Dates must be in YYYY-MM-DD format inside QUERY.

Example 6: Combining QUERY with IMPORTRANGE

Fetch Sales greater than 10,000 from another Google Sheet:

=QUERY(IMPORTRANGE("sheet_url","Sales!A1:D100"), "select Col1, Col3 where Col3 > 10000", 1)

👉 When using IMPORTRANGE inside QUERY, you use Col1, Col2 instead of A, B, C.

Read More: IMPORTRANGE in Google Sheets

Example 7: Using SUM / GROUP BY in QUERY

Show total sales by department:

=QUERY(A1:D100, "select B, sum(C) group by B", 1)

👉 Groups data by department and sums up sales.

Example 8: QUERY with Multiple Conditions (AND / OR)

Employees in “Sales” department AND sales above 10,000:

=QUERY(A1:D100, "select A, B, C where B = 'Sales' and C > 10000", 1)

Employees in “HR” OR “Finance”:

=QUERY(A1:D100, "select A, B where B = 'HR' or B = 'Finance'", 1)

Example 9: QUERY with LIMIT and OFFSET

Show only top 5 salespersons:

=QUERY(A1:D100, "select A, C order by C desc limit 5", 1)

Skip first 10 rows and show the rest:

=QUERY(A1:D100, "select A, C offset 10", 1)

Example 10: QUERY with LIKE (Partial Match)

Find employees whose name starts with “Ra”:

=QUERY(A1:D100, "select A, C where A like 'Ra%'", 1)

👉 % is used as a wildcard in LIKE.

Example 11: QUERY with COUNT

Count number of employees in each department:

=QUERY(A1:D100, "select B, count(A) group by B", 1)

Example 12: QUERY Across Multiple Sheets (via IMPORTRANGE + Array)

If you want to combine data from two different Google Sheets and then apply QUERY:

=QUERY(
  {IMPORTRANGE("sheet_url1","Sales!A1:C100");
   IMPORTRANGE("sheet_url2","Sales!A1:C100")},
  "select Col1, sum(Col3) group by Col1",
  1
)

👉 This merges two files and gives total sales by employee.

Read More: ARRAYFORMULA in Google Sheets

QUERY vs FILTER vs Pivot Table

Feature	QUERY	FILTER	Pivot Table
Ease of Use	Medium (need SQL-like syntax)	Easy	Easy
Flexibility	Very High	Moderate	High
Can Group & Summarize	✅ Yes	❌ No	✅ Yes
Live Updates	✅ Yes	✅ Yes	✅ Yes
Best For	MIS Reports, Data Analysis	Quick Filtering	Summaries, Dashboards

👉 Tip: Start with FILTER if you are new, but move to QUERY for advanced MIS reports.

Common Problems and Fixes in QUERY

Problem: QUERY not recognizing dates.
- Fix: Use date 'YYYY-MM-DD' format inside query.
Problem: QUERY returning wrong headers.
- Fix: Adjust the [headers] parameter (usually 1).
Problem: “Unable to parse query string” error.
- Fix: Check spaces, commas, single vs double quotes. QUERY is strict about syntax.
Problem: Too slow with big ranges.
- Fix: Limit your range (A1:D5000 instead of A:D).

Common MIS Use Cases

Filter employees by department or sales target.
Generate quick reports without pivot tables.
Merge data from multiple sheets using IMPORTRANGE + QUERY.
Create monthly or yearly reports with GROUP BY.

Pro Tips

Always use Col1, Col2 when combining QUERY with IMPORTRANGE.
Wrap QUERY inside IFERROR() to avoid ugly error messages. =IFERROR(QUERY(A1:D100,"select A,C where C>5000",1),"No Data Found")
For dynamic ranges, use open-ended ranges like A2:D instead of A2:D1000.
QUERY can act like a database → think in terms of tables, rows, and filters, not just cells.

What’s Next?

In the next post, we’ll learn about the FILTER function in Google Sheets

Spread the love