Why QUERY function basics in Google Sheets? - Purpose & Use Cases

The Big Idea

What if you could get any data you want from your spreadsheet with just one simple formula?

The Scenario

Imagine you have a big list of sales data in a spreadsheet. You want to find only the sales from last month or just the sales above a certain amount. Doing this by looking through each row and copying data manually is tiring and takes forever.

The Problem

Manually searching and copying data is slow and easy to mess up. You might miss some rows or copy wrong cells. If the data changes, you have to do it all over again. It's frustrating and wastes a lot of time.

The Solution

The QUERY function lets you ask your spreadsheet questions like a database. You can quickly filter, sort, and pick exactly the data you want with one simple formula. It updates automatically when your data changes, saving you time and mistakes.

Before vs After

✗ Before

Copy rows one by one that match criteria

✓ After

=QUERY(A1:D100, "select A, B where C > 100")

What It Enables

With QUERY, you can instantly get the exact data you need from large tables without any manual searching or copying.

Real Life Example

A store manager uses QUERY to quickly see all sales over $500 last month, helping them decide which products to reorder.

Key Takeaways

Manually filtering data is slow and error-prone.

QUERY lets you ask your spreadsheet questions easily.

It saves time and updates results automatically.

Practice

1. What does the QUERY function do in Google Sheets?

easy

A. It deletes rows based on conditions.

B. It creates charts automatically from your data.

C. It lets you ask questions to your data using a simple language.

D. It sorts data alphabetically only.

Solution

1. Step 1: Understand the purpose of QUERY

   The QUERY function is designed to let you ask questions to your data using a language similar to SQL.

2. Step 2: Compare options with QUERY's function

   The other options describe other functions or actions, but QUERY specifically helps to filter, select, and group data by asking questions.

3. Final Answer:

   It lets you ask questions to your data using a simple language. -> Option C

4. Quick Check:

   QUERY = ask questions to data [OK]

Hint: QUERY lets you ask questions to your data in words [OK]

Common Mistakes:

• Thinking QUERY creates charts
• Confusing QUERY with sorting only
• Assuming QUERY deletes data

2. Which of these is the correct syntax to select column A and B from range A1:C10 using QUERY?

easy

A. =QUERY(A1:C10, "SELECT Col1, Col2")

B. =QUERY(A1:C10, SELECT A, B)

C. =QUERY(A1:C10, 'SELECT A, B')

D. =QUERY(A1:C10, "SELECT 1, 2")

Solution

1. Step 1: Check the query string format

   The query must be a text string inside double quotes, so "SELECT Col1, Col2" is correct.

2. Step 2: Validate the column references

   Columns are referenced by ColN (Col1 for column A, Col2 for B), not plain numbers in QUERY, so "SELECT 1, 2" is invalid.

3. Final Answer:

   =QUERY(A1:C10, "SELECT Col1, Col2") -> Option A

4. Quick Check:

   Query string in quotes + ColN = correct syntax [OK]

Hint: Always put query inside double quotes with Col1, Col2 [OK]

Common Mistakes:

• Forgetting quotes around the query
• Using single quotes instead of double quotes
• Using numbers instead of ColN

3. Given this data in A1:B4:

A | B
1 | 10
2 | 20
3 | 30
4 | 40

What is the result of =QUERY(A1:B4, "SELECT A WHERE B > 20")?

medium

A. [3, 4]

B. [1, 2]

C. [2, 3, 4]

D. [10, 20, 30, 40]

Solution

1. Step 1: Understand the query condition

   The query selects column A where column B is greater than 20. Values in B are 10, 20, 30, 40.

2. Step 2: Filter rows where B > 20

   Only rows with B=30 and B=40 satisfy this, so corresponding A values are 3 and 4.

3. Final Answer:

   [3, 4] -> Option A

4. Quick Check:

   Filter B > 20 gives A = 3,4 [OK]

Hint: Filter rows by condition, then pick column values [OK]

Common Mistakes:

• Including rows where B equals 20
• Returning values from wrong column
• Confusing output format

4. What is wrong with this QUERY formula?
=QUERY(A1:C10, "SELECT A WHERE B = 'Apple')"

medium

A. Using single quotes inside double quotes is not allowed.

B. Missing closing double quote for the query string.

C. Column B cannot be used in WHERE clause.

D. The range A1:C10 is too large for QUERY.

Solution

1. Step 1: Check the quotes in the query string

   The query string starts with a double quote but ends with a single quote before the closing double quote, causing a syntax error.

2. Step 2: Identify correct quote usage

   Inside the query string, single quotes are allowed around text values, but the entire query must be enclosed properly in double quotes.

3. Final Answer:

   Missing closing double quote for the query string. -> Option B

4. Quick Check:

   Query string must start and end with double quotes [OK]

Hint: Check matching quotes around the whole query string [OK]

Common Mistakes:

• Confusing inner single quotes with outer quotes
• Thinking column B can't be used in WHERE
• Assuming range size causes errors

5. You have sales data in A1:C6:

Product | Region | Sales
Apple   | East   | 100
Banana  | West   | 150
Apple   | West   | 200
Banana  | East   | 120
Apple   | East   | 130
Banana  | West   | 170

Which QUERY formula correctly shows total sales per product?

hard

A. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A")

B. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B")

C. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100")

D. =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product")

Solution

1. Step 1: Identify correct column labels and aggregation

   Headers are Product, Region, Sales. To get total sales per product, select Product and SUM(Sales) grouped by Product.

2. Step 2: Check query syntax and grouping

   =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") uses correct header labels and groups by product. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A") uses letters A,C which do not match headers. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100") misses GROUP BY. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B") groups by Region (B).

3. Final Answer:

   =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") -> Option D

4. Quick Check:

   Use header labels + GROUP BY product for totals [OK]

Hint: Use column header names and GROUP BY to sum per group [OK]

Common Mistakes:

• Using column letters instead of header names
• Forgetting GROUP BY clause
• Grouping by wrong column