How to Limit Rows and Columns in an Excel Worksheet

Here's How to Display Only What You Want to See

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 very rarely is all that room is required. Fortunately, you can limit the number of columns and rows that your spreadsheet displays.

When Limiting Rows and Columns Is Desirable

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

Most people 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, you can place it in an area of the worksheet where other users can't reach it. Or, if less experienced users need to access your worksheet, limiting where they can go will 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. The range you want to block must be contiguous— with no gaps in the listed cell references.

A Note of Caution

Changing the scroll area is a temporary measure. It is reset each time the workbook is closed and reopened.


Using the steps below, you can change the properties of a worksheet to limit the number of rows to 30 and the number of columns to 26.

  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.
  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:
    1. Scroll below row 30 or to the right of column Z; or
    2. Click on a cell to the right of or below cell Z30 in the worksheet.

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 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. Here's how 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 two through five above to hide all columns after column Z.
  7. Save the workbook. 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 two to three above to unhide all columns.