LIMIT and OFFSET in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see a small, specific part of the sales data to review recent transactions without scrolling through the entire list.

📊 Data: You have a table with sales transactions including Date, Product, Region, and Sales Amount.

🎯 Deliverable: Create a table that shows only 5 sales records starting from the 6th record in the data.

Sample Data

Date	Product	Region	Sales Amount
2024-01-01	Shirt	East	100
2024-01-02	Pants	West	150
2024-01-03	Hat	North	50
2024-01-04	Shoes	South	200
2024-01-05	Shirt	East	120
2024-01-06	Pants	West	180
2024-01-07	Hat	North	70
2024-01-08	Shoes	South	220
2024-01-09	Shirt	East	130
2024-01-10	Pants	West	160

1

Step 1: Select a cell where you want to display the limited data, for example, cell F2.

Expected Result

Cell F2 is selected and ready for formula input.

2

Step 2: Enter the formula to get 5 sales records starting from the 6th record in the sales data table. Use the QUERY function with LIMIT and OFFSET.

=QUERY(A1:D11, "SELECT A, B, C, D LIMIT 5 OFFSET 5", 1)

Expected Result

The table shows data rows 6 to 10 from the original data, including headers.

3

Step 3: Check that the displayed data matches the original data rows 6 to 10 exactly.

Expected Result

Displayed rows: 2024-01-06 Pants West 180; 2024-01-07 Hat North 70; 2024-01-08 Shoes South 220; 2024-01-09 Shirt East 130; 2024-01-10 Pants West 160.

Final Result

Date       | Product | Region | Sales Amount
------------------------------------------
2024-01-06 | Pants   | West   | 180
2024-01-07 | Hat     | North  | 70
2024-01-08 | Shoes   | South  | 220
2024-01-09 | Shirt   | East   | 130
2024-01-10 | Pants   | West   | 160

✓You can use LIMIT and OFFSET in QUERY to show a specific part of your data.

✓This helps focus on recent or specific records without scrolling.

✓The formula =QUERY(A1:D11, "SELECT A, B, C, D LIMIT 5 OFFSET 5", 1) extracts 5 rows starting from the 6th data row.

Bonus Challenge

Modify the formula to show the next 5 rows after the current 5, i.e., rows 11 to 15, and handle the case if there are fewer than 5 rows left.

Show Hint

Change the OFFSET number to 10 and keep LIMIT 5. Use IFERROR to handle fewer rows gracefully.

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