How to Apply Custom Freeze Panes in Excel (3 Easy Ways)

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.


Download Practice Book

Download the practice book.


3 Ways to Apply Custom Freeze Panes in Excel

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.

Custom Rows and Columns Freezing with Freeze Panes in Excel

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.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • To freeze Columns C & B and Rows 6 & 7, we need to select Cell D8.

Custom Rows and Columns Freezing with Freeze Panes in Excel

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.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • You will see a horizontal line and a verticle line have occurred in the worksheet like below.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • Now, if we scroll down, we will see Row 6 & 7 are locked.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • Similarly, if we scroll left to right, we will see Columns C & B are also locked.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • Again, to freeze any specific rows, select the row just below the rows you need to freeze. Here, we have selected Row 9.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • 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.

Custom Rows and Columns Freezing with Freeze Panes in Excel

  • Use the previous steps to see results like below.

Read More: Excel Freeze Panes Not Working (5 Causes with Fixes)


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.

Customized Locking with Excel Magic Freeze Button

  • Secondly, select ‘More Commands’ from the drop-down menu.

Customized Locking with Excel Magic Freeze Button

  • Thirdly, select ‘Freeze Panes’ from the ‘Choose commands from’ Then click ‘Add’ and OK to include it in the toolbar.

Customized Locking with Excel Magic Freeze Button

  • After that, a new icon will appear in the Quick Access Toolbar. It is the Freeze Panes magic button.

Customized Locking with Excel Magic Freeze Button

  • Now, select Column C to lock Columns A & B.

Customized Locking with Excel Magic Freeze Button

  • Next, select the Freeze Panes icon from the tab and select Freeze Panes from the drop-down menu.

Customized Locking with Excel Magic Freeze Button

  • Finally, you will see Columns A & B are frozen like below.

Customized Locking with Excel Magic Freeze Button

Read More: How to Freeze Multiple Panes in Excel (4 Criteria)


Similar Readings:


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.

Use of Keyboard Shortcuts to Custom Freeze Panes

  • Next, hit the Alt key and we will see a ribbon like below.

Use of Keyboard Shortcuts to Custom Freeze Panes

  • Now, press W from the keyboard. It will take you to the View tab.

Use of Keyboard Shortcuts to Custom Freeze Panes

  • Then, press F. It will open the drop-down menu of Freeze Panes.

Use of Keyboard Shortcuts to Custom Freeze Panes

  • Again press F to freeze the desired columns.

Use of Keyboard Shortcuts to Custom Freeze Panes

Read More: Keyboard Shortcut to Freeze Panes in Excel (3 Shortcuts)


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.

Freeze Rows & Columns with VBA in Excel

  • 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

Freeze Rows & Columns with VBA in Excel

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.

Read More: How to Freeze Panes with VBA in Excel (5 Suitable Ways)


Things To Remember

When you try to freeze rows or columns, you need to remind 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.

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo