In this article, we will learn to use custom freeze panes in Excel. We need to use freeze panes when we have to keep specific rows or columns visible. We require this feature in the case of large datasets. You can easily lock the first row or column from the ‘Freeze Panes’ option directly. Today, we will use the ‘Freeze Panes’ option to lock any rows or columns we want.
How to Apply Custom Freeze Panes in Excel: 3 Ways
1. Custom Rows and Columns Freezing with Freeze Panes in Excel
We can freeze any rows or columns in our Excel worksheet using the ‘Freeze Panes’ tool. In the following method, we will talk about locking custom rows and columns. For this purpose, we will use a dataset that describes the sales amount of the first six months of some salesmen.
Follow the steps below to know this method.
STEPS:
- In the first place, you need to know which rows and columns you want to freeze. We will lock Column C & B and Row 6 & 7.
- To freeze Columns C & B and Rows 6 & 7, we need to select Cell D8.
To lock columns and rows simultaneously, you need to select a cell just below the row you want to freeze. The cell should also be selected from the next column you want to freeze.
- Now, go to the View tab and select Freeze Panes.
- After that, a drop-down menu will occur. Select Freeze Panes from there.
- You will see a horizontal line and a verticle line have occurred in the worksheet like below.
- Now, if we scroll down, we will see Row 6 & 7 are locked.
- Similarly, if we scroll left to right, we will see Columns C & B are also locked.
- Again, to freeze any specific rows, select the row just below the rows you need to freeze. Here, we have selected Row 9.
- After that, go to the View tab and select Freeze Panes like mentioned above.
- Then, if you scroll down, you will see Rows 6, 7 & 8 are frozen.
- Finally, to freeze a column, just select the column next to it.
- Use the previous steps to see results like below.
Read More: Excel Freeze Panes Not Working
2. Customized Locking with Excel Magic Freeze Button
We can save time and energy to freeze any rows or columns with a customized Quick Access Toolbar.
Observe the steps below for the magic freeze button.
STEPS:
- Firstly, go to the ‘Customize Quick Access Toolbar’ icon in the up-left corner of the screen.
- Secondly, select ‘More Commands’ from the drop-down menu.
- Thirdly, select ‘Freeze Panes’ from the ‘Choose commands from’ Then click ‘Add’ and OK to include it in the toolbar.
- After that, a new icon will appear in the Quick Access Toolbar. It is the Freeze Panes magic button.
- Now, select Column C to lock Columns A & B.
- Next, select the Freeze Panes icon from the tab and select Freeze Panes from the drop-down menu.
- Finally, you will see Columns A & B are frozen like below.
Read More: How to Freeze Multiple Panes in Excel
3. Use of Keyboard Shortcuts to Apply Custom Freeze Panes
We can also lock panes easily with the help of keyboard shortcuts easily. This is another way to freeze any rows or columns you want.
Here, the keyboard shortcut is Alt + W + F + F.
Pay attention to the steps below.
STEPS:
- First, select the immediate next column of the column we need to freeze. We have selected Column D here because we want to freeze Columns A, B & C.
- Next, hit the Alt key and we will see a ribbon like below.
- Now, press W from the keyboard. It will take you to the View tab.
- Then, press F. It will open the drop-down menu of Freeze Panes.
- Again press F to freeze the desired columns.
Read More: Keyboard Shortcut to Freeze Panes in Excel
Freeze Rows & Columns with VBA in Excel
Excel VBA also gives us the opportunity to freeze rows, columns, and cells in our dataset as we want or customize. In this section, we will try to freeze rows and columns with VBA code. We will use the previous dataset here.
Follow the steps below to know more.
STEPS:
- In the beginning, go to the Developer tab and select Visual Basic.
- Then, go to Insert and select Module.
- Type the code in Module and save it to lock rows.
Sub Lock_Row()
ActiveWindow.FreezePanes = False
Rows("8:8").Select
ActiveWindow.FreezePanes = True
End Sub
Here, we have locked the rows above Row 8. So we put “8:8” in the code.
- Next, go to Macros from the Developer.
- After, select Run from the Macro.
- If you run the code, you will see rows above Row 8 are frozen.
- To freeze a specific column, type the code below.
Sub Lock_Column()
ActiveWindow.FreezePanes = False
Columns("C:C").Select
ActiveWindow.FreezePanes = True
End Sub
- After running the code, it will freeze Columns A & B.
- To freeze, rows and columns simultaneously, type the code below.
Sub Lock_Cell()
ActiveWindow.FreezePanes = False
Range("D8").Select
ActiveWindow.FreezePanes = True
End Sub
- After running the code, it will freeze Columns A, B & C, and rows above Row 8.
Things To Remember
When you try to freeze rows or columns, you need to remember certain things.
- You cannot lock columns or rows in the middle of your worksheet. You can only freeze rows above the selected row and columns on the left side of the worksheet. Suppose, if you try to freeze Columns C & E, it won’t happen. Instead, Columns A, B, C & D will be frozen.
- The Freeze Panes command will not work when you are in editing mode. To cancel edit mode, press the Esc key.
Download Practice Book
Download the practice book.
Conclusion
Here, we have discussed some methods on how we can use custom freeze panes in Excel. I hope these methods will help you to know everything about custom freeze panes. Furthermore, the practice book is also added in the beginning. Download and exercise the practice book to know more about freeze panes. Last of all, if you have any suggestions or queries, feel free to ask in the comment box.
