How to Use the SQL Limit and Offset Clause in Google Sheets

Do you find yourself working with large amounts of data in your Google Sheets? It can quickly become overwhelming, but don’t worry! There’s a solution. In this article, we’ll explore how to use the LIMIT function to restrict the number of entries copied into your spreadsheet.

Limiting the Number of Entries

To apply the LIMIT clause, use the following query:

select * limit 10

In this query, the asterisk (*) after the select clause indicates that all columns will be selected. The number 10 after the LIMIT command restricts the results to 10 rows. Here’s the complete formula:

=query(week27!A2:H633, "select * limit 10")

Here’s the result of this query:

Sorting the Entries and Limiting Results

You can combine the LIMIT clause with the ORDER BY clause to retrieve entries with the smallest or largest values in a specific column. For example, to list the ten orders with the smallest profit, use the following query:

select * order by H limit 10

In this query, profits are stored in column H. Here’s the complete formula:

=query(week27!A2:H633, "select * order by H limit 10")

Here’s the result of this query:

If you want to list the ten orders with the largest profit, simply add the DESC clause. The query will be:

select * order by H desc limit 10

Here’s the complete formula:

=query(week27!A2:H633, "select * order by H desc limit 10")

And here’s the result:

Limiting Entries with a Specific Value

You can also restrict the results to entries with a specific value. For example, if you want to retrieve the ten first orders of grain bread, use the following query:

select * where B contains 'grain bread' limit 10

In this query, column B refers to the second column where the item is listed. Here’s the complete formula:

=query(week27!A2:H633, "select * where B contains 'grain bread' limit 10")

And here’s the result:

Limiting Entries from a Specific Date

Often, your data source will contain a column with dates and/or times. To restrict the selection to a specific date, add the DATE clause in addition to LIMIT and CONTAIN. For example, if you want to list the ten first entries from June 30th, the specific query will be:

select * where A contains date '2020-06-30' limit 10

Here’s the complete formula:

=query(week27!A2:H633, "select * where A contains date '2020-06-30' limit 10")

And here’s the result:

The trick here is to add the DATE clause so that Google Sheets can process the date using the correct format instead of treating it as a regular string.

Skipping the First Entries with Offset

Lastly, you can skip the first entries using the OFFSET clause. For example, to skip the first 50 entries, use the following query:

select * offset 50

Here’s the complete formula:

=query(week27!A2:H58, "select * offset 50")

We intentionally reduced the range to demonstrate how the OFFSET clause works. Here’s the result:

You can also combine OFFSET with LIMIT. For example, to skip the first 50 entries and retrieve the next 10, use the following query:

select * limit 10 offset 50

The order is important: if you add OFFSET before LIMIT, the query won’t work. Here’s the complete formula:

=query(week27!A2:H633, "select * limit 10 offset 50")

And here’s the result:

Example Sheet

The best way to understand how something works is by trying it out. Here’s an example sheet you can use to see how the LIMIT clause works in Google Sheets.

SQL Limit Sample Sheet

Now that you know how to use the LIMIT and OFFSET clauses in Google Sheets, managing large amounts of data in your spreadsheets will be a breeze. Enjoy!