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.