Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This feature is useful when you want the header rows or columns to remain stationary when using the using the sheet in a data grid format.
To use this function, place the cursor in a cell in the row below the row which you want to freeze and in the column to the right of the column you wish to freeze. For example, if you want the headers in row 3 and above and data in columns A and B to remain stationary, place the cursor in cell C4. Then choose the View Ribbon and click on the Freeze Panes button. This will give three options:
- Freeze Panes: This will freeze based on the cursor selection
- Freeze Top Row: This will freeze just the top row (row 1)
- Freeze First Column: This will freeze just the first column (column A)
The image below shows the Freeze Pane options.
When choosing option 1, if the cursor is in column A it will only freeze rows above the cursor, with no impact on columns. Likewise if the cursor is in row 1 it will only freeze columns to the left of the cursor, no impact on rows.
As an example, the image below shows an extract of a time tracking sheet. The headers for the In and Out columns are in row 1 and then there are several weeks worth of data below. As you can see from the screen shot row 1 is visible and then immediately below it is row 182. This is because row 1 has been frozen using the Freeze Panes feature. If the row had not been frozen the header row would have disappeared when scrolling down. Although this is a very simplistic example, when using a more complicated input sheet the user may have input data in the incorrect columns if the headers were not visible. Also, with this example, the Freeze Panes feature has been used for the dates in column B. If the user were to scroll right, the dates would stay visible so that further data to the right can still be aligned with the correct date.
These examples show the feature being used in a Data list/grid scenario. However it can also be used in other scenarios.
I have on occasions found that I have wanted the whole contents of a sheet, not just headers, to stay stationary; for example a report or an input sheet that fits on one screen and the user shouldn't have to move the "report" around the screen but scrolling up/down or left/right or moving the cursor would move the report off the screen.
If you select a cell at least one column beyond and at least one row below the bottom right of the report and freeze panes at that point, the report stays in the top left of the screen no matter how much scrolling up or down or cursor movement is applied. Bearing in mind that other users may have different size screens, I would suggest that the cursor is placed only a few rows and columns beyond the report.
The image below shows an input sheet for a similar time tracking sheet. This time the Freeze Panes feature has been implemented beyond the bottom right of the input area, in cell AE18. This can be seen by the black lines which appear on the row and column grid lines.
One minor downside with this method is that when you press the Home key the cursor will go to the column to the right of the report rather than the far left.
To get round this though, select the report area and Unlock the cells.
By default all cells are locked. The Unlock option is found in a couple of places. Right click on one of the selected cells and choose Format Cells from the right click menu. The Locked options are on the protection Tab of the Format pop-up.
Alternatively, still with the report area selected, from the Home ribbon select the Format Cells button and in the list of options below there is an Option for Lock cells. If the cells are locked the padlock icon next to the option will be activated/highlighted. Click the option and the selected cells will be Unlocked, the padlock icon will de-activate.
Locking of cells is only effective when the Sheet is also Protected. From the Review ribbon choose "Protect Sheet" and in the list of options deselect the option for selecting Locked cells. Clicking OK will then give an option for a password which will have to be repeated. Doing this means the only cells of the sheet that are selectable are those in the report.
Unless you have changed the Transition Navigation settings in Options, the Home key would normally take the cursor to the far left of the active row. Ctrl + Home would normally take the cursor to the top left of the sheet.
With this method the Home key now takes the cursor to the top left cell regardless of Transition Navigation settings. Ctrl + Home does not move the cursor at all.
The unlocking of cells can be limited to just individual input cells within the report rather than the whole report area if so required with some added benefits.
With the Select Locked Cells options deselected when the cursor reaches the far right of the selectable area, continuing to press the right arrow key will put the cursor back to the first column on the next row. if the cursor reaches the bottom right of the report the next available cell will be back at the top left of the report.
Also, if the option for moving the cursor when Enter is pressed is selected the cursor will move to the next unlocked cell, again this would be back to the top left if the cursor has reached the bottom right.
If any of the above needs explaining further, please contact me via Message.