How to Limit Rows and Columns in an Excel Worksheet

Display only what you want to see

A woman working on a computer.

damircudic/Getty Images

Each worksheet in Excel can contain more than 1,000,000 rows and 16,000 columns of information, but very rarely is all that room necessary for day to day projects. Fortunately, you can limit the number of columns and rows that your spreadsheet displays. Additionally, you can restrict access to individual areas of a worksheet.

For example, to avoid accidental changes to specific 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 spreadsheet, limiting where they can go will keep them from getting lost in the empty rows and columns.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Limit Worksheet Rows in Excel with VBA

Screenshot of VBA Editor showing the ScrollArea limited

You can use Visual Basic for Applications (VBA) to temporarily limit the range of usable rows and columns in the Scroll Area property of the worksheet.

Changing the scroll area is a temporary measure; it resets each time the workbook is closed and reopened.

In this example, you'll 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. Click File > Close and Return to Microsoft Excel.

Now, test your worksheet to ensure your change is applied. If you try to scroll past row 30 or column Z, the spreadsheet should bounce you back to the selected range. You won't be able to edit cells outside that range.

Removing Scrolling Restrictions

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 you save the workbook, the VBA editor adds the dollar signs to make the cell references in the range absolute.

Hide Rows and Columns in Excel

An alternative method for restricting the work area of a worksheet is to hide the unused rows and columns; they'll stay hidden even after you close the document. 

Excel spreadsheet with Hide selected on right click menu.

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.

    Press and release the right arrow key instead of the down arrow to hide the columns to the right.

  7. Save the workbook; the columns and rows outside the range A1 to Z30 will remain hidden until you unhide them.

Unhide Rows and Columns in Excel

If you change your mind or made a mistake, you can unhide the rows and columns and start over.

Excel's Format drop-down menu open with Hide & Unhide/UnHide Rows selected.

To unhide row 31 and higher and column Z and higher:

  1. Click on the row header for row 30 — or the last visible row in the worksheet — to select the entire row.

  2. Then, while right-clicking with the mouse, scroll down to the hidden section.

  3. Click the Home tab of the ribbon.

  4. In the Cells section, click Format > Hide & Unhide > Unhide Rows to restore the hidden rows.

    You can also right click on the row header and select Unhide from the drop-down menu.

  5. Click on the column header for ​column AA — or the last visible column — and repeat steps two to four above to unhide all columns.