Seeing Different Excel Worksheet Areas by Freezing Cells and Splitting Panes

Having an area of a worksheet visible while working on another area can be really helpful.

For instance, you may want to scroll through your worksheet while keeping headings visible, or you may want to look at how totals at the end change as you enter new variables at the top of the spreadsheet. This can become really handy when dealing with large spreadsheets where you aren’t able to have all the information you need within one area.

There are two ways to keep certain cells visible in Microsoft Excel: you can either lock specific rows or columns by freezing panes, or split panes to create multiple worksheet areas that you can view separately.

1. Freezing Cells

There are some reasons to consider freezing cells, which essentially keeps specific rows or columns visible when you scroll in the worksheet. For example, if the first row in your spreadsheet contains labels, you could freeze that row so that the column labels remain visible as you scroll down in your spreadsheet.

To lock rows or columns:

  1. To lock rows, select the row below the row or rows that you want to keep visible when you scroll. Or, to lock columns, select the column to the right of the column or columns you want to remain visible.
  2. On the VIEW tab, [Click] FREEZE PANES in the WINDOW group.
  3. [Click] FREEZE PANES.

Note: To lock both rows and columns, instead of selecting a row or column, select the cell below and to the right of the rows and columns you want to keep visible when you scroll.

Unfreezing Panes:

  1. On the VIEW tab, [Click] FREEZE PANES in the WINDOW group.
  2. From the options, choose UNFREEZE PANES.

There are some limitations, however, to freeze panes. For instance, you can only freeze rows at the top and columns on the left side of the worksheet. You can’t freeze rows and columns in the middle of the worksheet. Also, you cannot freeze panes when a worksheet is protected.

2. Splitting Panes

Splitting panes can be a great solution when you need to see different parts of a large spreadsheet at the same time. When a worksheet is split into different areas, each contains a separate view of the same worksheet, and changes made in one worksheet area will automatically appear in the other worksheet areas.

For instance, a pane could have a formula that calculates the total
if you make changes

  • To split panes, choose  in in the WINDOW group on the VIEW tab. Or, [Drag] the area at the top of the vertical scroll bar or at the right end of the horizontal scroll bar.
  • Once you’ve created panes, you can change the area of the panes. Position the mouse over the thick line between panes (the pointer changes to a split pointer  or ), and [Drag] the line to change where the worksheet is split.
  • To see different areas within split panes, either use the scroll bars on the right and bottom of the worksheet, or select an individual cell and use the arrow keys to move to the desired worksheet area.

Unsplitting Panes

  • To remove all split panes at once, in the Window group, [Click] SPLIT in the WINDOW group on the VIEW tab.
  • To remove the split between any two scrollable areas, [Double-Click] any part of the split bar that divides the panes.

You cannot have split panes and freeze panes at the same time, but you can switch between one and another depending on what you need at the time. Either way, these tools can make it easier to work with spreadsheet data no matter where it is in the worksheet.

Have you used the freeze pane, or split pane tools to make it easier to work with an Excel worksheet? Or do you have any other tips for working with large worksheets? Please let us know in the comments!

Share!