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.