How to Limit Rows and Columns in an Excel Worksheet

Here's how to display only what you want to see

woman working on a computer

Getty Images/damircudic

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 for day to day projects. Fortunately, you can limit the number of columns and rows that your spreadsheet displays. Additionally, you can limit access to specific areas of a worksheet.

For an 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.

Limit Worksheet Rows in Excel with VBA

Screenshot of VBA Editor showing the ScrollArea limited

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 reference.

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.

Now, test your worksheet to ensure your modification has been applied. You should not be able to scroll below row 30 or to the right of column Z.

Screenshot of Excel showing limited selection in Excel
Orange shading is to show accessible areas of the spreadsheet after VBA modification.

Removing Limiting Modifications

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.

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.

Hide Rows and Columns in Excel

Screenshot of Excel showing how to hide rows

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 Rows and Columns in Excel

Screenshot of Excel showing how to unhide rows

If the workbook is saved to keep the rows and columns hidden when it is re-opened, the following steps will assist you in unhiding 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.