In this article:

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.

How to limit rows in Google Sheets: Highlight a row

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.

How to limit rows in Google Sheets: Select rows

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.

How to limit rows in Google Sheets: Hide Rows

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. 

How to limit rows in Google Sheets: Results

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.

How to limit rows in Google Sheets: Select a row

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.

How to limit rows in Google Sheets: Highlight rows

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.

How to limit rows in Google Sheets: Right click menu

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.

How to limit rows in Google Sheets: Results

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.

How to limit rows in Google Sheets: Select an empty cell

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)

How to limit rows in Google Sheets: Enter the formula

3. Press enter to see the results

Press the enter key, the results are populated to your specified parameters. 

How to limit rows in Google Sheets: Results

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 and trying our rent reminder app as well!

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started