Introduction
Are you struggling to figure out how to limit rows in Google Sheets? Although there isn’t a direct feature to do so, fret not! In this article, we’ll walk you through different methods to limit rows in Google Sheets using hiding or deleting row functionalities, all in just a few simple steps.
For the following demonstrations, we have created a sample dataset. Click here to access the spreadsheet and follow along at your own pace.
Limiting the Number of Rows Using the Hide Rows Function
We can easily hide all the unnecessary rows in Google Sheets, thus applying a reversible limit while preserving the data in the hidden rows.
-
Select the row number after the desired limit.
- Click on the row number until the row that you want to limit. For example, if we want to have only 30 rows, we need to select row 31.
-
Use the keyboard shortcut Ctrl (Cmd ⌘ on Mac), Shift, and the down arrow.
- Press the Ctrl (Cmd ⌘ on Mac), Shift, and the down arrow keys simultaneously. This will select all the rows below the selected row, including the selected row itself. In our example, all the rows below and including row 31 will be highlighted.
-
Right-click and select “Hide rows x-1000”.
- Right-click on one of the highlighted row numbers and select “Hide rows x-1000”, where x represents the initially selected row number. In our example, it will display “Hide rows 31-1000”.
-
Check the results.
- All the rows below and including the initially selected row are now hidden. An upward arrow indicator 🢑 is displayed next to the last row number, allowing you to restore/show the hidden rows if needed.
- In our example, the rows are now limited up to row 30.
Limiting the Number of Rows Using the Delete Rows Function
We can apply a row limit in a sheet by deleting all the rows below the required number.
-
Click on the row number at the end of the desired limit.
- Click on the row number until the row that you want to limit. In our example, we will limit the rows to 14, so we need to select row number 15.
-
Use the keyboard shortcut Ctrl (Cmd on Mac), Shift, and the down arrow.
- Press the Ctrl, Shift, and the down arrow keys together. For Mac users, it will be the Cmd ⌘, Shift, and the down arrow keys. This will highlight all the rows below the selected row, including the selected row itself. In our example, all the rows below and including row 15 will be highlighted.
-
Right-click and select “Delete rows x-1000”.
- Right-click on one of the row numbers and select “Delete rows x-1000”, where x is the initially selected row number. In our example, it will be “Delete rows 15-1000”.
-
Check the results.
- All the rows below and including the target row are deleted. Note that this includes the data in that range. As you can see in our example, the rows are now limited up to row 14.
- If you need to add more rows, you can do so by entering a quantity in the input field next to “Add more rows at the bottom” at the bottom of the page. Then click the “Add” button to add the desired number of new rows.
Limiting Rows Using the Array_Constrain Formula
The array_constrain function is used to limit the number of rows in a range. We will utilize this function using the following formula:
=array_constrain(Cell Range, Number of Rows, Number of Columns)
-
Select an empty cell next to your data.
- Highlight an empty cell next to your data, ensuring that there is enough space for the results to be displayed.
-
Enter the array_constrain formula
=ARRAY_CONSTRAIN(Cell Range, Number of Rows, Number of Columns).- In the empty cell, enter the formula:
=ARRAY_CONSTRAIN(Cell Range, Number of Rows, Number of Columns)
Formula Breakdown:
=ARRAY_CONSTRAIN: This instructs Google Sheets to return a result as an array based on the specified constraints.Cell Range: The range of cells to include in the function, entered in the form of cell references. In our example, our data starts at cell B3, and to include all the data, we need to set the end of the range to D13.Number of Rows: The number of rows to include in the returned data. We will set it to 11 in our example, which includes the 11 rows of data in the returned results. To further limit this, we would adjust this number to include fewer/more rows.Number of Columns: The number of columns to include in the returned data. Since our data has 3 columns, we will set this number to 3.
To illustrate this formula in our example, we would enter:
=ARRAY_CONSTRAIN(B3:D13, 11, 3)
- Press Enter to see the results.
- Press the Enter key, and the results will be displayed based on the specified parameters.
We hope this article has helped you gain a better understanding of how to limit rows in Google Sheets. You may also find our article on how to group rows in Google Sheets and our tutorial on how to freeze rows in Google Sheets useful.
By the way, we recommend checking out our guide on how to attach Google Sheets to an email and trying out our rent reminder app.
For more tips and tricks on Google Sheets and other marketing-related topics, feel free to visit Crawlan.com.
