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

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
You can't have more than 1,048,576 rows in an Excel spreadsheet.
Image Credit: marekuliasz/iStock/Getty Images

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.

Advertisement

Hide All Rows Below a Specific Row

Video of the Day

Step 1

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.

Video of the Day

Step 2

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.

Advertisement

Step 3

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 4

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.

Advertisement

Temporarily Disable Scrolling and Editing

Step 1

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.

Advertisement

Step 2

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.

Advertisement

Step 3

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.

Advertisement

Advertisement

references