.png)
How to Limit Rows in Google Sheets (Step-By-Step Guide)
How to Limit Rows in Google Sheets
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.
Limit Number of Rows Using the Hide Rows Feature
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.
1. Highlight the number of the row after the desired limit
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.
2. Press Ctrl (Cmd ⌘ on Mac), Shift and Down Key
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.
3. Right click and select Hide rows x - 1000
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.
4. See the results
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.
Limit Number of Rows Using the Delete Rows Feature
We can apply a limit to the rows in a sheet by deleting all rows below the amount required.
1. Click on the number of the row at the end of the desired limit
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.
2. Press Ctrl (Cmd on Mac), Shift and Down
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.
3. Right click and select Delete rows x - 1000
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.
4. See the results
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.
Limit Rows Using the Array_Constrain Formula
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.
1. Select an empty cell next to your data
At the side of your data highlight an empty cell, ensure there is adequate room for the results to populate.
2. Enter the array_constrain formula =ARRAY_CONSTRAIN(Cell range, Row amount,Column amount)
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)
3. Press enter to see the results
Press the enter key, the results are populated to your specified parameters.
