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.
- First, go to the worksheet where you want to lock the top row.
- Next, go to the View tab, select Freeze Top Row from the Freeze Panes drop-down.
- Consequently, the top row of our dataset is frozen. A gray straight line under the row indicates the locked row.
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.
➤ 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.
- 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).
- Then, go to the View tab, Choose Freeze Panes from the Freeze Panes drop-down.
- 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.
- First, go to the Customize Quick Access Toolbar drop-down and click on ‘More Commands’.
- Then, choose Freeze Panes from the Popular Commands list and click on ‘Add>>’. Now, press OK.
- 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.
- After that, go to the Quick Access Toolbar, select the Freeze Panes from the Freeze Panes drop-down.
- Finally, the first 3 rows of the dataset are now locked.
- How to Collapse Rows in Excel (6 Methods)
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- Hide Rows Based on Cell Value in Excel (5 Methods)
- How to Group Rows in Excel with Expand or Collapse (5 Methods)
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:
- 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.
- Next, go to the View tab and select Split from the Window.
- 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.
- Firstly, go to the worksheet and select Cell C5 (right to the column and below to the row you want to freeze).
- 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.
- In the beginning, go to the worksheet, right-click on the sheet name and click on View Code.
- 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
- Then, Run the code.
- Finally, here is our output.
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.