LIMIT and OFFSET in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Using OFFSET to get a range starting from row 3

Given a list of numbers in cells A1:A10, which formula correctly returns 5 cells starting from the 3rd row?

A =OFFSET(A1,3,0,5,1)

B =OFFSET(A1,2,0,5,1)

C =OFFSET(A1,2,0,4,1)

D =OFFSET(A1,1,0,5,1)

💡 Hint

Remember OFFSET counts rows starting from zero offset.

The OFFSET function starts counting rows from the reference cell. To start from row 3, you offset by 2 rows (since A1 is row 1). The height parameter is 5 to get 5 cells.

📊 Formula Result

intermediate

Using LIMIT with QUERY to get top 3 rows

You have data in A1:B10. Which QUERY formula returns only the first 3 rows of data?

A =QUERY(A1:B10,"SELECT * LIMIT 3")

B =QUERY(A1:B10,"SELECT * OFFSET 3")

C =QUERY(A1:B10,"SELECT * LIMIT 5")

D =QUERY(A1:B10,"SELECT * LIMIT 10 OFFSET 3")

💡 Hint

LIMIT controls how many rows to return from the start.

The QUERY function with 'LIMIT 3' returns only the first 3 rows of the data range.

❓ Function Choice

advanced

Choosing the correct formula to skip first 4 rows and get next 3 rows

Which formula correctly returns 3 rows starting after skipping the first 4 rows from data in A1:A10?

A All of the above

B =QUERY(A1:A10,"SELECT * LIMIT 3 OFFSET 4")

C =INDEX(A1:A10,5,1):INDEX(A1:A10,7,1)

D =OFFSET(A1,4,0,3,1)

💡 Hint

Think about how OFFSET, QUERY, and INDEX can be used to select ranges.

All three options correctly select 3 rows starting after skipping 4 rows:

• A uses OFFSET to start at row 5 (offset 4) and height 3.
• C uses INDEX to get cells from row 5 to 7.
• D uses QUERY with OFFSET 4 and LIMIT 3.

📊 Formula Result

advanced

What is the output of this QUERY with LIMIT and OFFSET?

Given data in A1:A6 as {10,20,30,40,50,60}, what is the output of =QUERY(A1:A6,"SELECT Col1 LIMIT 2 OFFSET 3")?

A 10, 20

B 30, 40

C 50, 60

D 40, 50

💡 Hint

OFFSET skips rows, LIMIT picks how many rows after that.

OFFSET 3 skips first 3 rows (10,20,30). LIMIT 2 picks next 2 rows: 40 and 50.

🎯 Scenario

expert

Extract a dynamic range using OFFSET and COUNTA

You have a list of names in column A starting at A1. The list length changes daily. Which formula dynamically returns the entire list without empty cells below?

A =OFFSET(A1,1,0,COUNTA(A:A),1)

B =OFFSET(A1,0,0,COUNTA(A:A)-1,1)

C =OFFSET(A1,0,0,COUNTA(A:A),1)

D =OFFSET(A2,0,0,COUNTA(A:A),1)

💡 Hint

COUNTA counts non-empty cells. OFFSET starts at A1 with zero offset.

OFFSET starting at A1 with zero row offset and height equal to COUNTA(A:A) returns the full list including A1. Other options either skip the first row or miscount the height.

Practice

1. What does the LIMIT function do in Google Sheets when used with QUERY?

easy

A. It controls how many rows are returned from the data.

B. It controls which columns are shown in the result.

C. It changes the order of the rows returned.

D. It filters rows based on a condition.

Solution

1. Step 1: Understand the role of LIMIT in QUERY

   The LIMIT clause sets the maximum number of rows to return from the data.

2. Step 2: Compare with other options

   LIMIT does not filter or reorder data; it only limits the count of rows returned.

3. Final Answer:

   It controls how many rows are returned from the data. -> Option A

4. Quick Check:

   LIMIT = number of rows returned [OK]

Hint: LIMIT sets max rows to show, like a page size [OK]

Common Mistakes:

• Thinking LIMIT filters rows by condition
• Confusing LIMIT with OFFSET
• Assuming LIMIT changes column selection

2. Which of the following is the correct syntax to get 5 rows starting from the 4th row using QUERY in Google Sheets?

easy

A. =QUERY(A1:C10, "SELECT * OFFSET 3 LIMIT 5")

B. =QUERY(A1:C10, "SELECT * LIMIT 5 OFFSET 3")

C. =QUERY(A1:C10, "SELECT * LIMIT 3 OFFSET 5")

D. =QUERY(A1:C10, "SELECT * OFFSET 5 LIMIT 3")

Solution

1. Step 1: Recall QUERY syntax order

   In QUERY, the correct order is OFFSET first, then LIMIT.

2. Step 2: Match the correct numbers

   We want 5 rows starting from row 4 (OFFSET 3 means skip first 3 rows), so OFFSET 3 LIMIT 5 is correct.

3. Final Answer:

   =QUERY(A1:C10, "SELECT * OFFSET 3 LIMIT 5") -> Option A

4. Quick Check:

   OFFSET before LIMIT, numbers match [OK]

Hint: Use OFFSET then LIMIT in QUERY string [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET order
• Mixing up OFFSET and LIMIT numbers
• Using LIMIT before OFFSET in QUERY

3. Given the data in cells A1:A10 as numbers 1 to 10, what will the formula =QUERY(A1:A10, "SELECT A LIMIT 3 OFFSET 4") return?

medium

A. 1, 2, 3

B. 4, 5, 6

C. 6, 7, 8

D. 5, 6, 7

Solution

1. Step 1: Understand OFFSET and LIMIT effect

   OFFSET 4 skips the first 4 rows (values 1,2,3,4), so data starts at 5.

2. Step 2: LIMIT 3 picks next 3 rows

   After skipping 4 rows, LIMIT 3 returns 5, 6, and 7.

3. Final Answer:

   6, 7, 8 -> Option C

4. Quick Check:

   OFFSET 4 skips first 4, LIMIT 3 picks next 3 [OK]

Hint: OFFSET skips rows, LIMIT picks how many after [OK]

Common Mistakes:

• Counting OFFSET as inclusive
• Confusing OFFSET number with LIMIT
• Starting count from zero instead of one

4. You wrote =QUERY(A1:B10, "SELECT A LIMIT 5 OFFSET 2") but it returns an error. What is the likely problem?

medium

A. OFFSET cannot be used without ORDER BY clause.

B. The syntax order LIMIT then OFFSET is correct; error is elsewhere.

C. LIMIT must come after OFFSET in QUERY.

D. OFFSET value is too large for the data range.

Solution

1. Step 1: Check QUERY syntax rules

   LIMIT must come before OFFSET in QUERY, which is correct here.

2. Step 2: Consider other causes

   OFFSET can be used without ORDER BY, and 2 is not too large for 10 rows.

3. Final Answer:

   The syntax order LIMIT then OFFSET is correct; error is elsewhere. -> Option B

4. Quick Check:

   LIMIT before OFFSET is correct syntax [OK]

Hint: LIMIT before OFFSET; error likely outside these clauses [OK]

Common Mistakes:

• Thinking OFFSET needs ORDER BY
• Swapping LIMIT and OFFSET order
• Assuming OFFSET too large causes error

5. You have a table with 100 rows. You want to display rows 21 to 30 using QUERY. Which formula correctly does this?

hard

A. =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 19")

B. =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 21")

C. =QUERY(A1:D100, "SELECT * LIMIT 9 OFFSET 20")

D. =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 20")

Solution

1. Step 1: Calculate OFFSET for row 21

   OFFSET counts how many rows to skip, so to start at row 21, skip 20 rows.

2. Step 2: Set LIMIT for 10 rows

   Rows 21 to 30 is 10 rows, so LIMIT 10 is correct.

3. Final Answer:

   =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 20") -> Option D

4. Quick Check:

   OFFSET 20 skips 20 rows, LIMIT 10 picks next 10 [OK]

Hint: OFFSET = rows to skip; LIMIT = rows to show [OK]

Common Mistakes:

• Using OFFSET 21 instead of 20
• Setting LIMIT less than 10
• Confusing OFFSET with starting row number