While there is no feature to directly limit rows in google sheets, we can apply a number of workarounds to achieve this.
In this article we will show you the various methods to limit rows in google sheets by using the hide or delete rows feature in just a few simple steps.
For the following demonstrations an example data set has been created. Click here to access the sheet and follow along at home.
We can hide all rows in Google Sheets below what is required. This applies an easily reversible limit to the number of rows that will keep any existing data in the rows removed.
For this example we will limit the rows to 30 rows, we will therefore need to hide the other rows in the sheet.
Click the number of the row you want the limit to apply up to. For example, we only want to have 30 rows in our sheets so we need to select row 31.
Use the keyboard shortcut Ctrl (Cmd ⌘ on Mac), Shift and the down arrow key. This will select all rows below your selected row inclusive. In our example all rows below and including row 31 are highlighted.
Right click on one of the highlighted row numbers and click Hide rows x-1000 where x is the number of the originally selected row. As can be seen in our example Hide rows 31-1000 is displayed.
All rows including and below the originally highlighted row are now hidden. An upwards arrow indicator 🢑 now displays next to the bottom row number which can be used to return/unhide the rows if required.
The rows are now limited up to row 30 in our example.
We can apply a limit to the rows in a sheet by deleting all rows below the amount required.
Click the number of the row you want the limit to apply up to. For our example we will limit the rows to 14 rows, we will therefore select row number 15.
Click the keyboard shortcut Ctrl, Shift and the down arrow. For Mac users it will be Cmd ⌘, Shift and the down arrow key combination. This will highlight all rows below your selected row inclusive. In our example all rows below and including row 15 are highlighted.
Right click on one of the row numbers and select the option marked “Delete rows x-1000” where x is the number of the originally selected row. In our example this is row 15 - 1000.
All rows including and below the target row are deleted. Note that this also includes any data in that range. As you can see in our example the rows are now limited up to row 14.
If you need to re-add more rows this can be achieved by entering an amount in the input box next to “more rows at the bottom” at the bottom of the page. Then click the Add button to the left of the input box to add the amount of new rows.
The array_constrain function is used to limit the amount of rows in an array. We will use this function by using the formula:
=array_constrain(Cell Range, Row amount,Column amount)
Syntax Breakdown:
=array_constrain: This tells Google Sheets to return an array result based on following specified constraints.
Cell range: The range of cells to be included in the function.
Row amount: The amount of rows to be included in the returned data. This will be used to limit the return rows.
Column amount: The amount of columns to include in the returned data.
At the side of your data highlight an empty cell, ensure there is adequate room for the results to populate.
In the empty cell enter the formula:
=ARRAY_CONSTRAIN(Cell Range, Row amount,Column amount)
Formula Breakdown:
=array_constrain: This tells Google Sheets to return an array result based on following specified constraints.
Cell range: The range of cells to be included in the function and entered as cell references. In our example our data starts at cell B3, to include all of the data we need to set the range end to D13.
Row amount: The amount of rows to be included in the returned data. We will set this to 11 in our example. This will include all 11 rows in our data in the returned results. To limit this further we would adjust this number to include less/more rows.
Column amount: The amount of columns to include in the returned data. As our data has 3 columns we will set this number to 3
To demonstrate this formula in our example we will enter
=ARRAY_CONSTRAIN(B3:D13, 11, 3)
Press the enter key, the results are populated to your specified parameters.
We hope this article has helped you and given you a better understanding on how to limit rows in Google Sheets. You might also like our article on grouping rows in Google Sheets and our tutorial on how to collapse rows.
On a side note, we also recommend reading our guide on how to attach Google Sheets to an email.
