LIMIT and OFFSET in Google Sheets - Step-by-Step Guide

Introduction

LIMIT and OFFSET help you show only a part of your data in Google Sheets. This is useful when you want to see a small section of a big list, like showing only 5 rows starting from row 11.

When you want to display the first 10 sales records from a large list.

When you need to skip the first 5 rows and show the next 10 rows of data.

When creating a summary table that only shows a small part of a big dataset.

When you want to paginate data, showing a few rows per page.

When you want to extract a specific block of rows from a table for analysis.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

You start entering the formula

💡 Start with =QUERY to use LIMIT and OFFSET

Step 3: Enter

- the formula bar

Formula is typed like =QUERY(A1:C100, "SELECT * LIMIT 5 OFFSET 10")

💡 Replace A1:C100 with your data range

Step 4: Press

- Enter key

The cell shows 5 rows of data starting from row 11 of the original range

Step 5: Adjust

- the LIMIT and OFFSET numbers in the formula

You change how many rows to show and where to start

Before vs After

Before

A table with 100 rows of sales data in A1:C100

After

A formula =QUERY(A1:C100, "SELECT * LIMIT 5 OFFSET 10") shows rows 11 to 15 from the original data

Settings Reference

LIMIT

📍 Inside the QUERY formula's query string

Sets how many rows to show from the data

Default: No limit means all rows are shown

OFFSET

📍 Inside the QUERY formula's query string

Skips a number of rows before starting to show data

Default: 0 means start from the first row

Common Mistakes

Using OFFSET without LIMIT in QUERY

OFFSET alone does not limit rows, so it shows all rows after the offset, which can be too many

Always use LIMIT with OFFSET to control how many rows appear

Typing LIMIT and OFFSET outside the QUERY formula string

LIMIT and OFFSET must be inside the query string in quotes, otherwise the formula breaks

Write LIMIT and OFFSET inside the second argument of QUERY, like "SELECT * LIMIT 5 OFFSET 10"

Summary

LIMIT and OFFSET let you show a specific part of your data in Google Sheets.

They work inside the QUERY formula as part of the query string.

Use LIMIT to set how many rows to show and OFFSET to skip rows before showing.

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