Recently, I volunteered to help a friend on a project which required delivering a weekly Excel summary. When I asked my friend how the spreadsheet looked, he hesitated. He looked me in the eyes and said, “It’s too complicated”. My immediate reaction was disappointment. I knew the one I produced was simpler, but I didn’t know what “too complicated” meant. It turns out the data was fine. I simply needed to show him how to freeze the Excel columns to make scrolling easier.
My friend wanted frozen columns so he could still see names as he scrolled through the spreadsheet. Sure enough, I had locked the top row, but not bothered to lock any Excel columns.
Locking Excel Columns and Rows
Excel has a nice feature called Freeze Panes that can solve such a problem. This feature allows you to keep information from scrolling off the screen when moving through a spreadsheet. As a matter of habit, I always freeze the second row. What I failed to do with my friend’s sheet was to freeze the first columns so he could see the person’s name as he moved across the spreadsheet. You can see an example of this problem below.
If you scroll across, you lose columns A and B, which provide context. You no longer can tell Pat Bitow is mapped to 27 West 68th Street.
On an Excel spreadsheet with 10 records, this isn’t too much of an issue. However, my friend’s spreadsheet contained 18,000 records, which makes it “too complicated”. The good news is using Freeze Panes is easy to set.
To freeze columns and rows in Excel,
- From the View menu, verify you’re using Normal.
- Click the row number just below the area you’d like to freeze. The whole row should highlight.
- Click the cell on that highlighted row to the right of the columns you would like to freeze.
- From the Window menu, select Freeze Panes.
As example, if I click cell C2 on the spreadsheet below, I’m freezing the First and Last columns as well as the top row with the column headings. I’ve outlined this area in red.
If I scroll to the right to see the more information, these columns remain locked as shown below by the blue shading. Columns D through F have scrolled off, but not columns A and B that are frozen.
Editor’s Note: Since Microsoft Excel 2007, the Freeze Pane function is available from the View tab on the Excel ribbon.