LIMIT and OFFSET in Google Sheets - Dashboard Guide

Dashboard Mode - LIMIT and OFFSET

Goal

Show how to display a limited number of rows from a data table and skip some rows using formulas in Google Sheets.

Sample Data

Row	Product	Sales
1	Apples	100
2	Bananas	150
3	Cherries	200
4	Dates	250
5	Elderberries	300
6	Figs	350
7	Grapes	400

Dashboard Components

• Component 1: Display first 3 rows (LIMIT 3)
  =ARRAY_CONSTRAIN(A2:C8, 3, 3)
  Shows the first 3 rows of the data table.
• Component 2: Display 3 rows starting from row 4 (OFFSET 3, LIMIT 3)
  =INDEX(A2:C8, SEQUENCE(3), SEQUENCE(1,3)) with OFFSET logic:
  =INDEX(A2:C8, ROW(A5:A7)-ROW(A2)+1, COLUMN(A2:C2)-COLUMN(A2)+1)
  Or simpler:
  =ARRAY_CONSTRAIN(OFFSET(A2:C8, 3, 0), 3, 3)
  Shows rows 4 to 6 from the data.
• Component 3: Total sales of displayed rows
  =SUM(INDEX(A2:C8, SEQUENCE(3)+3, 3))
  Sum sales for rows 4 to 6 (Dates, Elderberries, Figs).

Dashboard Layout

+-----------------------------+
|  LIMIT 3 (Rows 1-3)          |
|  Apples, Bananas, Cherries   |
+-----------------------------+
|  OFFSET 3 + LIMIT 3 (Rows 4-6)|
|  Dates, Elderberries, Figs   |
+-----------------------------+
|  Total Sales of OFFSET rows   |
|  (Dates to Figs)             |
+-----------------------------+

Interactivity

User can change the number of rows to show by editing the number in the ARRAY_CONSTRAIN or OFFSET formulas. For example, changing LIMIT from 3 to 2 will show fewer rows. Changing OFFSET value will skip more or fewer rows.

Self Check

If you change the OFFSET value from 3 to 4, which rows will Component 2 show? What will be the new total sales?

Key Result

Dashboard shows how to use LIMIT and OFFSET formulas to display and sum parts of a data table in Google Sheets.

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