Limit Rows and Columns in an Excel Worksheet

Limit access to unused areas of a spreadsheet.

Rows and columns in a print spreadsheet
Getty Images

Each worksheet in Excel can contain more than 1,000,000 rows and more than 16,000 columns of information, but it isn't very often that all that room is required. Fortunately, you can limit the number of columns and rows that are shown in a spreadsheet.

Limit Scrolling by Limiting the Number of Rows and Columns in Excel

excel spreadsheet how to limit rows
Limit worksheet rows and columns in Excel by restricting the scroll area. (Ted French)

Mostly, we use considerably fewer than the maximum number of rows and columns and sometimes it might be an advantage to limit access to unused areas of the worksheet.

For example, to avoid accidental changes to certain data, it is sometimes useful to place it in an area of the worksheet where it can't be reached.

Or, if less experienced users need to access your worksheet, limiting where they can go can keep them from getting lost in the empty rows and columns that sit outside the data area.

Temporarily Limit Worksheet Rows

Whatever the reason, you can temporarily limit the number of rows and columns accessible by limiting the range of usable rows and columns in the Scroll Area property of the worksheet.

Note, however, that changing the Scroll Area is a temporary measure as it is reset each time the workbook is closed and reopened.

Furthermore, the range entered must be contiguous—no gaps in the listed cell references.

Example

The steps below were used to change the properties of a worksheet to limit the number of rows to 30 and the number of columns to 26 as shown in the image above.

  1. Open a blank Excel file.
  2. Right-click on the sheet tab at the bottom right of the screen for Sheet 1.
  3. Click View Code in the menu to open the Visual Basic for Applications (VBA) editor window.
  4. Find the Sheet Properties window in the bottom left corner of the VBA editor window.
  5. Find the Scroll Area property in the list of worksheet properties, as shown in the image above.
  6. Click in the empty box to the right of the Scroll Area label.
  7. Type the range a1:z30 in the box.
  8. Save the worksheet.
  9. Close the VBA editor window and return the worksheet.
  10. Test the worksheet. You should not be able to:
    • Scroll below row 30 or to the right of column Z;
    • Click on a cell to the right of or below cell Z30 in the worksheet.

Note: The image displays the entered range as $A$1:$Z$30. When the workbook is saved, the VBA editor adds the dollar signs ($) to make the cell references in range absolute.

Remove Scrolling Restrictions

As mentioned, the scroll restrictions only last as long as the workbook remains open. The easiest way to remove any scrolling restrictions is to save, close and reopen the workbook.

Alternatively, use steps two to four above to access the Sheet Properties of in the VBA editor window and remove the range listed for the Scroll Area property.

Limit Rows and Columns without VBA

An alternative and more permanent method for restricting the work area of a worksheet is to hide the unused rows and columns. 

These are the steps to hide the rows and columns outside the range A1 : Z30:

  1. Click on the row heading for row 31 to select the entire row.
  2. Press and hold the Shift and Ctrl keys on the keyboard.
  3. Press and release the Down Arrow key on the keyboard to select all rows from row 31 to the bottom of the worksheet.
  4. Right-click in the row headings to open the context menu.
  5. Choose Hide in the menu to hide the selected columns.
  6. Click on the column heading for column AA and repeat step 2-5 above to hide all columns after column Z.
  7. Save the workbook and the columns and rows outside the range A1 to Z30 will remain hidden.

Unhide Hidden Rows and Columns

If the workbook is saved to keep the rows and columns hidden when it is re-opened, the following steps will unhide the rows and columns from the example above:

  1. Click on the row header for row 30 — or the last visible row in the worksheet — to select the entire row.
  2. Click the Home tab of the ribbon.
  3. Click  Format > Hide & Unhide > Unhide Rows in the ribbon to restore the hidden rows.
  4. Click on the column header for ​column AA — or the last visible column — and repeat steps 2-3 above to unhide all columns.