How to Freeze Rows in Excel (6 Easy Methods)

In this article, we will discuss how to freeze rows in Excel. Often, we have to work with spreadsheets containing a larger number of rows and columns. As a result, managing these large numbers of rows while navigating through the spreadsheet becomes complicated. One of the easiest solutions to this problem is to freeze rows. Luckily, Excel has several ways to execute freezing rows.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


6 Methods to Freeze Rows in Excel

1. Freeze Top Rows in Excel Using Freeze Panes

Freezing Panes is one of the easiest options to lock rows in Excel. For example, we have a dataset containing students’ names and test scores. Now, we will apply the ‘Freeze Panes’ option to freeze the header row. So, here are the associated steps.

Steps:

  • First, go to the worksheet where you want to lock the top row.

Freeze Top Rows in Excel Using Freeze Panes

  • Next, go to the View tab, select Freeze Top Row from the Freeze Panes drop-down.

Freeze Top Rows in Excel Using Freeze Panes

  • Consequently, the top row of our dataset is frozen. A gray straight line under the row indicates the locked row.

Freeze Top Rows in Excel Using Freeze Panes

Unfreeze the Frozen Rows:

If you want to unfreeze the rows, then follow the below steps:

➤ Initially, go to the worksheet where the row is frozen.

Freeze Top Rows in Excel Using Freeze Panes

➤ Then, go to the View tab, select Unfreeze Panes from the Freeze Panes drop-down.


2. Lock Sets of Rows in Excel

Unlike the previous method, you can freeze a set of rows too. And, this time you can use the Freeze Panes option again. Let’s assume we have a dataset of students’ language-wise test scores. Now, if we want to freeze the first two rows of the dataset, then here are the involved steps.

Steps:

  • First, go to the worksheet where you want to freeze the first two rows. Next, select the row below the rows you want to lock (here, the 3rd row).

Lock Sets of Rows in Excel

  • Then, go to the View tab, Choose Freeze Panes from the Freeze Panes drop-down.

Lock Sets of Rows in Excel

  • As a consequence, the first two rows are locked.


3. Freeze Rows Using Quick Access Toolbar (Magic Freeze Button)

This time we will use the Quick Access Toolbar option to freeze rows. This method is very fast and works like magic.

Steps:

  • First, go to the Customize Quick Access Toolbar drop-down and click on ‘More Commands’.

Freeze Rows Using Quick Access Toolbar (Magic Freeze Button)

  • Then, choose Freeze Panes from the Popular Commands list and click on ‘Add>>’. Now, press OK.

Freeze Rows Using Quick Access Toolbar (Magic Freeze Button)

  • As a result, the Freeze Panes option is added to the Quick Access Toolbar.
  • Now, like Method 2, select the 5th row to lock rows 1 to 4.

Freeze Rows Using Quick Access Toolbar (Magic Freeze Button)

  • After that, go to the Quick Access Toolbar, select the Freeze Panes from the Freeze Panes drop-down.

Freeze Rows Using Quick Access Toolbar (Magic Freeze Button)

  • Finally, the first 3 rows of the dataset are now locked.


Similar Readings


4. Apply Split Option to Lock Rows

The Split option in Excel divides the working window into 2 areas where you can scroll a single dataset separately. By applying the Split option, you can work in different areas of a dataset simultaneously. The fun part is, when you work on one area, rows on the other area remain locked. So, the steps involved in this method are:

Steps:

  • First, select a row below the rows you want to freeze. Here, I have selected Row 8 as I want to see the first 7 rows of our dataset frozen.

Apply Split Option to Freeze Rows in Excel

  • Next, go to the View tab and select Split from the Window.

Apply Split Option to Freeze Rows in Excel

  • Subsequently, the following is our output.


5. Freeze Rows and Columns Simultaneously

Till now, in this article, we have discussed how to lock rows. Now, we will discuss how to freeze rows and columns simultaneously.

Steps:

  • Firstly, go to the worksheet and select Cell C5 (right to the column and below to the row you want to freeze).

Freeze Rows and Columns Simultaneously

  • Secondly, go to the View tab, Choose Freeze Panes from the Freeze Panes drop-down.

  • Finally, here the first 4 rows and 2 columns of the dataset are locked.


6. Excel VBA to Lock Rows and Columns

Likewise, described in Method 6, we can freeze rows and columns simultaneously by using VBA. Let’s assume, we want to lock the first 4  rows and Column 1 and Column 2 of our dataset.

Steps:

  • In the beginning, go to the worksheet, right-click on the sheet name and click on View Code.

Excel VBA to Lock Rows and Columns

  • Next, the Code Module will show up. Write the following code there.
Option Explicit
Sub ApplyFreezePanes()

ActiveSheet.Range("C5").Select
ActiveWindow.FreezePanes = True

End Sub

Excel VBA to Lock Rows and Columns

  • Then, Run the code.
  • Finally, here is our output.


Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Further Readings

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo