If you want to scroll down the worksheet, and lock the heading rows in place, so they’re always visible, you can use the Freeze Panes command. Be careful though, or you might end up with hidden rows that you can’t get to.
Freeze Panes Settings
In Excel 2007 and later versions, there is a Freeze Panes command on the Ribbon’s View tab. You can select one of the following commands, to freeze the rows and/or columns in place.
- Freeze Panes
- Freeze Top Row
- Freeze First Column
For Excel 2003 instructions, visit the Contextures website: FAQ: How do I lock the Title Row?
Freeze Top Row
If you select the Freeze Top Row command, the top visible row in the Excel window is frozen. You will not be able to scroll up to see any rows above the frozen row.
Freeze First Column
If you select the Freeze First Column command, the leftmost visible column in the Excel window is frozen. You will not be able to scroll left to see any columns to the left of the frozen column.
It doesn’t matter which cell is active when you apply the Freeze First Column command.
Freeze Panes
If you select the Freeze Panes command, the following table shows which rows and columns will be frozen, based on what you’ve selected on the worksheet, and which cell is active.
Selection | Active Cell | Frozen Rows | Frozen Columns |
Cell(s) | A1 | All rows above the centre of the Excel window | All columns to the left of centre in the Excel window |
Cell(s) | Any cell except A1 | All rows above the active cell | All columns to the left of the active cell |
Entire row(s) | A1 | All rows above the centre of the Excel window | All columns to the left of centre in the Excel window |
Entire row(s) | Any cell except A1 | All rows above the active cell | None |
Entire column(s) | A1 | All rows above the centre of the Excel window | All columns to the left of centre in the Excel window |
Entire column(s) | Any cell except A1 | None | All columns to the left of the active cell |
Entire worksheet | A1 | All rows above the centre of the Excel window | All columns to the left of centre in the Excel window |
Entire worksheet | Any cell except A1 | All rows above the active cell | All columns to the left of the active cell |
Prevent Mysterious Hidden Rows
Sometimes you want to hide rows on the worksheet, but it’s also possible to hide rows unintentionally, when you freeze the headings.
Before you freeze the title rows, make sure that all the rows and columns in the area to be frozen are visible, if you want to see them after freezing. For example, if Row 1 is out of view, you won’t be able to scroll up to that row later, if you freeze the rows.
In the screen shot below, there is a date in cell H1, and you can use the arrow keys to move to that cell, but can’t scroll up to see the row.
Follow the steps below to unfreeze the panes, then make sure all the rows and columns that you want in the headings are visible, before you freeze them again.
Unfreeze the Headings
If you need to reset the freeze panes, or don’t need them frozen any more, you can remove them.
- On the Ribbon’s View tab, click Freeze Panes, then click Unfreeze Panes.
Watch the Video
To see the steps for freezing the headings with the Freeze Panes command, and the problem with hidden rows, watch this short video.
Get the Sample File
To download a sample file to test the Freeze Panes options, go to the Excel Freeze Rows and Columns page on my Contextures site.
The workbook is in xlsx format, and does not contain any macros.
__________________
Got a file with this thing already actvie, wasted 30 mins trying to get rid of it then I came here, thanks for the info! Now all is fine 🙂
Thanks for the info! 🙂
Thank you for the info, very helpful!