Lock Excel Heading Rows in Place

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 2010 and 2007, 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

freezepanes03

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.

freezepanes04 

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.

freezepanes05 

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.

freezepanes02 

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.

freezepanes06

Watch the Video

To see the steps for freezing the headings with the Freeze Panes command, and the problem with hidden rows, please watch this short video tutorial.

__________________

You may also like...

2 Responses

  1. LokiPoki says:

    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 :)

  2. M says:

    Thanks for the info! :)

Leave a Reply to LokiPoki Cancel reply

Your email address will not be published. Required fields are marked *