QUERY function basics in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table lists people with their names, ages, and cities.

Cell	Value
A1	Name
B1	Age
C1	City
A2	Alice
B2	30
C2	New York
A3	Bob
B3	25
C3	Los Angeles
A4	Charlie
B4	35
C4	Chicago
A5	Diana
B5	28
C5	New York

Formula Trace

=QUERY(A1:C5, "SELECT Col1, Col2 WHERE Col3 = 'New York'", 1)

Step 1: QUERY(A1:C5, "SELECT Col1, Col2 WHERE Col3 = 'New York'", 1)

Step 2: Filter rows where C = 'New York'

Step 3: Select columns A and B from filtered rows

Step 4: Include header row from A1:C1 for columns A and B

Step 5: Final output

Cell Reference Map

     A       B       C
1 | Name  | Age   | City     |
2 | Alice | 30    | New York |
3 | Bob   | 25    | Los Angeles |
4 | Charlie | 35  | Chicago  |
5 | Diana | 28    | New York |

The QUERY function uses the range A1:C5, which includes headers and data rows.

Result

   A      B
1 | Name | Age |
2 | Alice| 30  |
3 | Diana| 28  |

The result shows only the Name and Age of people living in New York, including the header row.

Sheet Trace Quiz - 3 Questions

What does the QUERY function do in this example?

A Shows all data without filtering

B Selects all columns for people older than 30

C Selects Name and Age for people living in New York

D Selects only the City column

Key Result

QUERY(range, "SELECT columns WHERE condition", header_rows) filters and returns specified columns based on a condition.

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