📊 Lesson 4.4: QUERY — SQL-Like Power in a Single Cell

What QUERY Is

Everything you've done so far works one answer at a time: a SUM here, a lookup there. QUERY works on a whole table at once. You hand it a range and a short instruction written in a database-style language, and it returns a new table: just the columns you asked for, just the rows that match, sorted and summarized however you like — all recalculating live as the source data changes.

If you've ever met SQL (the language databases speak), QUERY will feel familiar; if you haven't, you're about to learn a tiny, friendly slice of it. Either way, here's the honest headline: no single Excel function takes a whole query-language string the way QUERY does. (Newer Excel functions such as FILTER and SORT cover parts of the job.) It's a genuine reason people choose Sheets, and one of the most empowering tools in this whole course.

Anatomy of a QUERY

Every QUERY has the same three parts:

=QUERY(data, "query string", headers)

• data — the range to query, e.g. Data!A1:E500 (include the header row).
• query string — your instruction, in quotes, e.g. "select A, C where D > 100 order by C desc".
• headers — how many header rows the data has (usually 1). This helps QUERY label its output.

Inside the query string you refer to columns by their spreadsheet letter — A, B, C — matching the columns of the data range you passed in. (When the data comes from an array or an import instead of a plain range, you refer to columns as Col1, Col2, … instead. For normal ranges, stick with letters.)

The Core Clauses

Here are the four clauses that do most of the work, with a sales table (Date, Region, Product, Amount in columns A–D) as the example:

You stack them into one string, in this order:

=QUERY(Data!A1:D500, "select B, D where D > 100 order by D desc limit 10", 1)

Read it as a sentence: "show me Region and Amount, for rows where Amount is over 100, sorted by Amount highest first, top 10." That single formula produces a live top-10 table that re-sorts itself the instant the data changes. Comparison operators work as you'd expect: =, <, >, <=, >=, !=, plus and, or, and contains for partial text matches.

Summarizing With GROUP BY

QUERY isn't just for filtering — it can summarize, replacing a whole stack of SUMIF formulas. Add an aggregate to your SELECT and a group by:

Total amount per region:
=QUERY(Data!A1:D500, "select B, sum(D) group by B order by sum(D) desc", 1)

Count of sales per product:
=QUERY(Data!A1:D500, "select C, count(A) group by C", 1)

The aggregate functions are sum, count, avg, max, and min. The rule to remember: every column in your SELECT must either be aggregated or appear in the group by. Want a cross-tab (regions down the side, products across the top)? Add a pivot clause: select B, sum(D) group by B pivot C.

By default the summary column gets an ugly header like "sum Amount." Fix it with label:

=QUERY(Data!A1:D500, "select B, sum(D) group by B label sum(D) 'Total Sales', B 'Region'", 1)

Interactive Queries & Pitfalls

The real magic is making a QUERY respond to the user. Because the query string is just text, you can build it with the & operator and reference a cell — say a region chosen from a dropdown in cell G1:

=QUERY(Data!A1:D500, "select C, sum(D) where B = '"&G1&"' group by C order by sum(D) desc", 1)

Now the report rebuilds instantly whenever someone picks a different region. That one pattern — a dropdown feeding a QUERY — is the beating heart of the interactive dashboard you'll build in the capstone.

Common errors and fixes

• Wrong quote types — double quotes wrap the whole string; single quotes wrap text values inside it.
• A non-aggregated, non-grouped column — every SELECT column must be in the aggregate or the group by.
• Column-letter mismatch — the letters refer to the data range's columns; if you change the range, recheck them.
• Mixed data types in a column — a column with both numbers and text can make QUERY guess wrong; clean the data first (Module 2 skills!).
• #REF! "result would overwrite" — QUERY outputs a whole table, so leave empty cells below and to the right for its results to "spill" into.

🎯 Project: A Live Report

You'll build a single-formula report that filters, sorts, and summarizes — and responds to a dropdown. This is a genuine taste of dashboard-building.

Step 1: =QUERY(Data!A1:D, "select *", 1)
Step 2: add   where D > 100
Step 3: add   order by D desc
Step 4: swap to a summary: select B, sum(D) group by B
Step 5: make it live: where B = '"&G1&"'  (G1 is a dropdown)

Best Practices for QUERY

✅ Do's

• Build it up clause by clause, checking the result at each step.
• Include the header row in the data range and pass 1 for headers.
• Clean your data first — consistent types make QUERY behave.

❌ Don'ts

• Don't mix quote types — double for the whole string, single for text values.
• Don't crowd the output — leave empty space for the result table to spill.
• Don't forget the group-by rule — every selected column must be aggregated or grouped.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You've reached the top of the power tier. Between lookups, the *IF(S) family, and now QUERY, you can pull data from anywhere, decide with logic, and reshape entire tables into live reports. That's genuinely advanced spreadsheet skill — the kind that impresses people at work. Module 4 complete.

❓ Common Questions at This Stage

🔭 Looking Ahead

Next — Lesson 5.1: Pivot Tables — Summarize Thousands of Rows in Seconds — we begin Module 5, Analysis & Visualization. You'll get the point-and-click cousin of QUERY: drag fields to summarize huge tables instantly, no formula required.

✅ Before the Next Lesson

• Get one interactive QUERY working with a dropdown — you'll reuse the pattern in the capstone
• Keep a data table of ~20+ rows handy for the pivot table lesson
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• QUERY function (Google Support)
• Google Sheets function list