How to Limit Rows and Columns in Excel

Display only what you want to see

A woman working on a computer.

damircudic/Getty Images

An Excel worksheet can contain more than 1,000,000 rows and 16,000 columns of information but rarely is all that room necessary for normal projects. Even when you have a worksheet with hundreds of cells populated, you may not need to see all that data once. To help minimize what you see at any given time, you can limit the number of columns and rows that your worksheet displays.

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

Limit Access to Rows and Columns With VBA

You can use Microsoft Visual Basic for Applications (VBA) to temporarily limit the range of usable rows and columns in a worksheet. In this example, you'll change the properties of a worksheet to limit the number of available rows to 30 and the number of columns to 26.

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

  1. Open a blank Excel file. At the bottom of the screen, right-click the Sheet1 sheet tab. From the menu, choose View Code.

    Excel with tab menu displayed
  2. The Visual Basic for Applications (VBA) editor window opens. In the left rail, locate the Properties section.

    MS Visual Basic for Applications in Excel
  3. Under Properties,in the right column of the ScrollArea row, click the empty box and type A1:Z30.

    MS Visual Basic for Applications in Excel
  4. Select File > Save and save your workbook as you normally would. Select File > Close and Return to Microsoft Excel.

    MS Visual Basic for Applications in Excel
  5. To make sure your change is applied, perform this test. In your worksheet, try to scroll past row 30 or column Z. If the change has been applied, Excel bounces you back to the selected range and you're unable to edit cells outside that range.

    Excel spreadsheet with row and column limits applied
  6. To remove the restrictions, access VBA again and delete the ScrollArea range.

Hide Rows and Columns in Excel

An alternative method for restricting the work area of a worksheet is to hide sections of unused rows and columns; they'll stay hidden even after you close the document. Follow the steps below to hide the rows and columns outside the range A1:Z30.

  1. Open your workbook and select the worksheet you want to hide rows and columns in. Click the header for row 31 to select the entire row.

    Excel worksheet with one row selected
  2. Press and hold the Shift and Ctrl keys on the keyboard. At the same time, press the down arrow key on the keyboard to select all rows from row 31 to the bottom of the worksheet. Release all the keys.

    Excel with rows 31 and below selected
  3. Right-click one of the row headings to open the contextual menu. Select Hide.

    Excel with contextual menu displayed
  4. The worksheet now shows only the data in rows 1 through 30.

    Excel worksheet with limited rows displayed
  5. Click the header for column AA and repeat steps 2 and 3 (using the right arrow key instead of the down arrow key) to hide all columns after column Z.

    Excel worksheet with some columns hidden
  6. Save the workbook; the columns and rows outside the range A1 to Z30 will remain hidden until you unhide them.

You can use the same process to hide any rows or columns you want. Just select the header or headers for the row or column, right-click the header, and select Hide.

Unhide Rows and Columns in Excel

When you want to view the data you hid, you can unhide the rows and columns at any time. Follow these steps to unhide the rows and columns you hid in the previous example.

  1. Open the worksheet you used to hide row 31 and higher and column AA and higher. Click the headers for row 30 (or the last visible row in the worksheet) and the row below it. Right-click the row headers and, from the menu, select Unhide.

    Excel with contextual menu displayed
  2. The hidden rows are restored.

    Excel worksheet with hidden rows restored
  3. Now click the headers for column Z (or the last visible column) and the column to the right of it. Right-click the selected column headers and, from the menu, choose Unhide. The hidden columns are restored.

    Excel with hidden columns restored