Three Different Ways to Limit Number of Rows in Google Sheets Query

0
426

If you ask me how to limit number of Rows in Google Sheets Query, I can give you different suggestions. What are they?

In Google Sheets you can follow three different ways to limit the number of rows returned by a Query formula. They are Two Query Clauses and then the Array_Constrain function. So we have three Query formula examples here, that can clear your doubts.

Limit Number of Rows in Google Sheets Query

Sample Data:

As already told, there are three different approaches to Limit Number of Rows in Google Sheets Query. The first two approaches limit the number of rows to certain number of rows from the beginning. That means you can say return only first 2 numbers,  3 numbers, 5 numbers like that. But the third approach is different. When you want to extract last certain number of rows in Google Sheets Query, you can depend the third approach. That means you can also limit number of rows to return to last few number of rows in Google Sheets Query.

Method # 1 Using the Array_Constrain Formula

First we can see how to limit the number of rows returned by a Query formula using the Array_Constrain formula. Here I’m using a basic Query formula.

=query(A1:D7,”Select *”)

The above basic Google Sheets Query formula would return all the above data as it’s. That means 7 rows and 4 Columns. Now see how I limit the number of rows returned by the Query.

=array_constrain(query(A1:D7,”Select *”),2,4)

This Query formula combined with Array_Constrain, returns only first two rows. At the end of this Query formula you can see the # 4. That denotes the total number of Columns.

Note: Here two rows means, the first row containing the label and the second row.

Method # 2 Using the Limit Clause in Query

This is the straight forward way to limit the number of output rows in Google Sheets.

=query(A1:D7,”Select * Limit 2″)

Note: Here two rows means, two rows excluding the row 1 which contain the Column Label. So the number of rows returning would be three.

Method # 3 Using the Offset Clause in Query [Return Last Few Rows in Google Sheets Query]

You may be using, used or heard about the Offset Function in Google Sheets. See how to use it in Query to limit the number of rows.

By using Offset in Query, you can limit the number of rows but only by skipping a given number of rows from the beginning.

In our above sample data, there are total 7 rows including the Column Label. You can use Offset clause in Google Sheets Query formula to return only the last certain number of rows.

The following Offset Clause in Google Sheets Query would return three rows including the Column Label.

=query(A1:D7,”Select * Offset 4″)

That means, this formula offsets 4 rows excluding the Column label.

Conclusion

This tutorial that about limit number of Rows in Google Sheets Query is not for newbies. Are you new to Google Sheets Query? Then please don’t forget to check our Google Sheets function guide.