LIMIT and OFFSET in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LIMIT function do in Google Sheets QUERY?

LIMIT restricts the number of rows returned by a QUERY to a specified number. For example, LIMIT 5 returns only the first 5 rows of the result.

beginner

What is the purpose of OFFSET in a Google Sheets QUERY?

OFFSET skips a specified number of rows before starting to return rows in a QUERY. For example, OFFSET 3 skips the first 3 rows and starts returning from the 4th row.

intermediate

How do LIMIT and OFFSET work together in a QUERY?

LIMIT and OFFSET can be combined to return a specific 'page' of rows. OFFSET skips rows, then LIMIT returns the next set. For example, LIMIT 3 OFFSET 5 returns rows 6, 7, and 8.

intermediate

Write a QUERY formula to get 10 rows starting from the 6th row of data in range A1:C100.

The formula is =QUERY(A1:C100, "SELECT * LIMIT 10 OFFSET 5", 1). OFFSET 5 skips first 5 rows, LIMIT 10 returns next 10 rows.

intermediate

Why might you use OFFSET in a QUERY instead of just filtering data?

OFFSET helps to skip a fixed number of rows, useful for paging through data or ignoring headers. Filtering changes which rows show based on conditions, OFFSET just skips rows by position.

What does OFFSET 3 do in a QUERY?

A Limits output to 3 rows

B Returns the first 3 rows

C Skips the first 3 rows

D Sorts data by 3rd column

OFFSET 3 skips the first 3 rows before returning data.

How do you limit a QUERY to show only 5 rows?

A Use LIMIT 5

B Use SORT 5

C Use FILTER 5

D Use OFFSET 5

LIMIT 5 restricts the QUERY output to 5 rows.

Which QUERY clause would you use to skip rows?

A OFFSET

B WHERE

C LIMIT

D ORDER BY

OFFSET skips a number of rows before returning results.

What does this QUERY do? =QUERY(A1:B10, "SELECT * LIMIT 3 OFFSET 2")

A Returns rows 1 to 3

B Returns rows 3 to 5

C Returns rows 2 to 4

D Returns all rows

OFFSET 2 skips first 2 rows, LIMIT 3 returns next 3 rows (rows 3,4,5).

Can LIMIT be used without OFFSET in a QUERY?

A Only with SORT

B No, OFFSET is required

C Only with FILTER

D Yes, to limit rows from the start

LIMIT alone limits rows starting from the first row.

Explain how LIMIT and OFFSET work together in a Google Sheets QUERY to show a specific set of rows.

Think about skipping some rows, then taking a few rows after.

Describe a real-life scenario where you might use OFFSET in a spreadsheet QUERY.

Imagine you want to see data starting from row 10, not the first rows.

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