Splitting, Freezing and Hiding Columns and Rows in Excel

A frustrating aspect of Microsoft Excel is that large spreadsheets can quickly become unwieldy, requiring the person viewing the document to endlessly scroll up and down, or left to right to get at the information they want to see. Comparing two pieces of information at either ends of a spreadsheet can be particularly difficult.

There are several solutions to this problem. Freezing columns allows the viewer to "lock in" the column headings and the first few fields of each record, so that the viewer can scroll through the spreadsheet while keeping the column headings and record names in view. Splitting columns allows the viewer to divide the screen into four quadrants, each of which can be scrolled independently. Hiding columns allows the viewer to temporarily hide columns or rows that are unimportant to them, leaving only that information on the screen that is useful to them at the time.

Freezing columns and rows
First look at your spreadsheet to determine where you want it to "freeze." In most cases you would probably want to freeze the first row (containing the column headings) and the first two columns (containing the first two fields in each record, such as first and last name). The way to do select these would be to select cell C2, as Excel will freeze columns and rows above and to the left of the cell you select.

After you've selected the appropriate cell, go to the Window menu, and choose Freeze Panes. Two dark lines will appear above and to the left of the cell you selected. These lines mark which areas of the spreadsheet you've chosen to freeze. Now, as you scroll horizontally or vertically inside the spreadsheet, the areas above and to the left of the lines will stay on your screen while the rest of the sheet is allowed to scroll.

To undo the command, go to the Window menu and choose Unfreeze Panes.

Splitting Columns and Rows
This is a similar operation to freezing columns and rows, except that in the end result, areas are not frozen, but rather can be scrolled independently. This is useful for comparing two different sections of a worksheet, or for cut-and-pasting information from one part of your spreadsheet to another. Start out by selecting where you want your spreadsheet to split. Again, your screen will split above and to the left of the cell you select (if you only want it to split into two windows, select within the gray column or row headings). Then, go to the Window menu and choose Split. This will create four windows on your spreadsheet. To select a window, click inside it. You can then move around inside this window without affecting the view in the other windows. The windows will scroll in pairs, meaning if you are scrolling up or down, the upper or lower pair will scroll together, if your are scrolling left or right, the left or right pairs will scroll together.

To undo the command, go to the Window menu and choose Remove Split.

Hiding Rows and Columns
To hide rows or columns of information you are not interested in viewing, select the gray row or column heading, right-click, and choose Hide. This will hide the entire row or column until you are ready to see it again. This is a useful technique if you are only interested in viewing or printing specific sections or a spreadsheet. You can hide as many of the columns or rows in a spreadsheet as you like, and they need not be contiguous.

To restore them, select the rows or columns on either side of the hidden row or column by left-clicking and dragging until both are highlighted. Then right-click, and choose Unhide. If you hid the first column (A), move your cursor to the left side of the first visible column (B) until you see a two-headed arrow, then simply drag the border to the right to reveal the hidden column. If you hid the first row, go to the top border of row 2 and drag down.

If you would like more information on these topics feel free to call me at 719-549-2045 or email me at jeffrey.wood@colostate.edu.