How to Use the QUERY Function with LIMIT and OFFSET in Google Sheets to Paginate Large Datasets

When analyzing large enterprise spreadsheets containing tens of thousands of transaction records, attempting to display all rows simultaneously causes visual clutter, slow rendering performance, and cumbersome vertical scrolling. In web development, databases handle massive datasets using SQL pagination. In Google Sheets, you can replicate this exact database pagination architecture using the QUERY function’s built-in LIMIT and OFFSET clauses. Pairing these clauses with interactive dropdown selectors or stepper cells allows you to build professional dashboard pagination controls.

Understanding the Syntax of LIMIT and OFFSET in QUERY

The Google Sheets QUERY function utilizes Google Visualization API Query Language, which closely mirrors standard SQL syntax:

=QUERY(data_range, "SELECT * LIMIT n OFFSET m", [headers])

How the clauses operate:

• LIMIT n: Sets the maximum number of rows returned in the output table (your page size, e.g., 20 rows per page).
• OFFSET m: Skips the specified number of rows before beginning to return records (e.g., skipping the first 40 rows to display Page 3).
• headers: Optional integer declaring the number of header rows at the top of data_range (typically 1).

Basic Demonstration: Fetching Specific Row Windows

Suppose you have a customer database occupying range A1:E5000 with column headers in Row 1.

To display only the first 10 customer records:

=QUERY(A1:E5000, "SELECT * LIMIT 10", 1)

To fetch the second page of 10 records (records 11 through 20), instruct QUERY to skip the first 10 rows:

=QUERY(A1:E5000, "SELECT * LIMIT 10 OFFSET 10", 1)

Building a Dynamic Pagination Control System

Rather than hardcoding numbers into your formula string, connect LIMIT and OFFSET to interactive input cells.

1. Set up cell G1 as your Page Size selector (e.g., enter 25).
2. Set up cell G2 as your Page Number selector (e.g., enter 1 for Page 1, 2 for Page 2).

The mathematical formula to determine the offset for any given page is:

Offset = (PageNumber - 1) * PageSize

Inject these cell references dynamically into the QUERY string using ampersand (&) concatenation:

=QUERY(A1:E5000, "SELECT * LIMIT " & G1 & " OFFSET " & ((G2 - 1) * G1), 1)

When the user changes the Page Number in cell G2 from 1 to 2, the formula calculates an offset of 25 and instantly displays rows 26 to 50.

Retaining Table Headers Across Every Page

When using OFFSET, the QUERY engine skips data rows, but standard headers might be omitted if the query skips into subsequent pages. To ensure column headers are permanently visible regardless of which page the user views:

1. Place your table column labels statically in Row 4 (e.g., copy headers from A1:E1).
2. In Row 5, write the pagination query referencing only the raw data body (A2:E5000) with headers declared as 0:

   =QUERY(A2:E5000, "SELECT * LIMIT " & G1 & " OFFSET " & ((G2 - 1) * G1), 0)

Calculating Total Available Pages Dynamically

To display an informative status indicator (such as “Page 2 of 15”), calculate total pages dynamically using COUNTA and CEILING:

="Page " & G2 & " of " & CEILING(COUNTA(A2:A5000) / G1)

The CEILING function rounds up to the nearest whole integer, ensuring partial final pages are accounted for.

Combining Pagination with Filtering and Sorting

In the QUERY grammar, LIMIT and OFFSET must always appear at the very end of the statement, after WHERE and ORDER BY clauses.

To filter for customers in the “West” region, sort by Order Total descending, and display the first page of 20 records:

=QUERY(A2:E5000, "SELECT A, B, C, D, E WHERE B = 'West' ORDER BY D DESC LIMIT 20 OFFSET 0", 0)

Clause Precedence Order in Google Sheets QUERY

Order	Clause Keyword	Functional Purpose
1	SELECT	Identifies columns to return (e.g., SELECT A, C).
2	WHERE	Filters rows based on boolean conditions.
3	ORDER BY	Sorts results ascending (ASC) or descending (DESC).
4	LIMIT	Restricts total row count returned (Page Size).
5	OFFSET	Skips specified row count before returning data.