When you have a lot of data that extends off screen — either down or to the right — you probably use Excel's Freeze Pane feature to keep your row or column headers visible while scrolling. It's a great feature, and it's easy to use. However, as we'll see, sometimes its behavior is a bit unexpected.
First, let's review how you freeze rows and columns in a worksheet. Simply select the cell below the header row and to the right of the header column and choose Freeze Panes from the Windows menu. Once you do, Excel will keep the row and column above and to the left of the selected cell on the screen, regardless of how far to the right or how far down you go in the actual spreadsheet. (Excel 2007 users, click the View tab, where you'll find the Freeze Panes tool in the Window group.)
That takes care of the basics, but it's easy to misunderstand just how the feature works. Remember, Excel freezes rows and columns above and/or to the left of the selection. So, for example:
Excel also has an unexpected feature that confuses a lot of users. If you select row 1, Excel determines the center of the window and places the freeze there. The same behavior is present if you select column A. I suppose it's meant to be a helpful shortcut, but it's not intuitive, although it is well documented in the Help files.
If you want to freeze only a header row — no column — simply select the row below the header and freeze away. Selecting the column to the right of column headers will do the same thing — freeze your column headers without freezing the top rows of your spreadsheet.
- If you select cell A2 or row 2, Excel will freeze row 1.
- If you select cell B1 or column B, Excel will freeze column A.
- If you select cell B2, Excel will freeze column A and row 1.
There's one more thing you might not know. You can freeze more than just the first row or the first column in a spreadsheet. The key is to select the cell below and to the right of the rows and columns, respectively that you want to freeze. For instance, if you select cell C3, Excel will freeze rows 1 and 2 and columns A and B.