How Do You Limit the Number of Rows in an Excel Spreadsheet?

If you want to allow other people to edit one of your spreadsheets in Microsoft Excel 2013 but don't want them to add more rows, limit the number of rows using one of two methods. You can hide all the rows below the last row of your spreadsheet or temporarily prevent people from scrolling below a certain row. Excel 2013 spreadsheets can have a maximum of 1,048,576 rows; you can select all of them and hide them, even if the rows are blank. To temporarily disable scrolling below a specific row, you must edit the VBA properties of the spreadsheet.

data spreadsheet on digital tablet
You can't have more than 1,048,576 rows in an Excel spreadsheet.
credit: marekuliasz/iStock/Getty Images

Hide All Rows Below a Specific Row

Step

Open the Excel spreadsheet by pressing "Ctrl-O," selecting the file and clicking "Open." Locate the last row you want to display in the spreadsheet.

Step

Select the entire row below the last row you want to display by clicking the row number. Don't select a single cell in the row accidentally.

Step

Hold "Ctrl-Shift" and press the "Down" arrow key to select all rows below the selected row. The last row is row number 1,048,576, so scrolling manually until you find it may take a while.

Step

Click "Format" in the Cells group on the Home tab, select "Hide & Unhide" and then click "Hide rows" to hide all rows. Only the rows that were not selected can be edited now. Press "Ctrl-S" to save the spreadsheet.

Temporarily Disable Scrolling and Editing

Step

Open the spreadsheet you want to edit, right-click the sheet name in the lower left corner of the window and choose "View Code" to open the VBA editor. Press "F4" to display the Properties window, if it is not displayed by default.

Step

Click inside the "ScrollArea" field in the Properties window. Type the number of the first row you want to display, a colon and then the number of the last row you want to display. For example, to let people scroll and edit only rows 1 through 100, type "1:100" (without the quotes) into the field.

Step

Press "Ctrl-S" and save the file on your hard drive. Close the Microsoft Visual Basic for Applications window and test the settings by scrolling through the spreadsheet. You will be unable to scroll past the row number you typed into the ScrollArea field. Even if you can see one more row under the last row, nothing happens when you click one of its columns. All other rows are not editable.