QUERY function basics in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the QUERY function do in Google Sheets?

The QUERY function lets you ask questions about your data, like filtering or sorting, using a simple language similar to SQL.

beginner

What is the basic syntax of the QUERY function?

QUERY(data, query, [headers]) where:
- data: the range of cells to query
- query: the text string with your question
- headers: optional number of header rows in data

beginner

In QUERY, how do you select only columns A and C from a data range?

Use SELECT A, C in the query string to pick only columns A and C.

beginner

How do you filter rows where column B is greater than 100 using QUERY?

Use WHERE B > 100 in the query string to show only rows where column B has values over 100.

beginner

What does the optional third argument in QUERY do?

It tells QUERY how many header rows your data has, so it knows which rows to treat as labels and not data.

What is the correct way to start a QUERY function in Google Sheets?

A =QUERY(data, "SELECT *")

B =QUERY(data, SELECT *)

C =QUERY(data, 'SELECT *')

D =QUERY(data, SELECT "*")

The query string must be inside double quotes within the formula, so =QUERY(data, "SELECT *") is correct.

Which part of QUERY specifies the data to analyze?

A The query string

B The data range

C The headers argument

D The output format

The first argument is the data range where QUERY looks for information.

How do you tell QUERY to only show rows where column C equals 'Apple'?

A "WHERE C = Apple"

B "WHERE C == 'Apple'"

C "WHERE C = 'Apple'"

D "WHERE C = \"Apple\""

Text values in QUERY must be inside single quotes inside the double-quoted query string.

What does SELECT * mean in a QUERY?

A Select all columns

B Select no columns

C Select only the first column

D Select only numeric columns

The asterisk (*) means select all columns from the data.

If your data has 1 header row, what should the third argument in QUERY be?

A 0

B It is not needed

C 2

D 1

The third argument tells QUERY how many header rows to skip; 1 means one header row.

Explain how to use the QUERY function to filter data by a condition.

Think about how to ask a question like 'show me rows where...'

Describe the role of the third argument in the QUERY function and why it matters.

Consider how QUERY treats the first rows of your data.

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