Have you been in a situation where you realized that knowing how to freeze 2 rows in Excel would have made your work and life easier? Excel gives you the option to freeze only the top row. But when your data’s header occupies the first two rows, that option does not offer the result you are looking for. So in this guide, we demonstrate multiple ways for freezing two rows.
Now, there are various ways it can be done. However, not all of them are foolproof. We are going to discuss 5 fully working methods for how to freeze 2 rows in Excel. This visual guide is going to be your most helpful source for this matter. We have included simple explanations and shortcuts for all the methods along with pictures for each step. We are also sharing with you the practice file for this guide.
Let’s not wait any further and dive in!
How Does Freezing Rows in Excel Work?
By default, Excel offers the option to freeze only the top row. It works pretty well until the point where the requirement is to freeze more than one row. Everything becomes slightly confusing at that moment. What to do if the second row from the top needs to be frozen as well?
Let’s take a look at an example below. Make sure to notice the row numbers in the picture as well.
Let’s say you need rows frozen because when you scroll down, you want to see the subject’s names (in Row 2) without having to scroll up and down every single time you forget which column’s data belongs to which subject. First, we are going to use the offered option Freeze Top Row in Excel and see if it does the job or gives us a clue.
Now, what happens when you scroll down?
You can see that the subjects’ names are also moved up with the sheet. It means that using the default option defeats the purpose of freezing a row in Excel. Therefore, you need a way to freeze the second row as well.
But how to do it? This is what we are going to discuss with proper demonstrations in this guide.
How to Freeze 2 Rows in Excel: 5 Effortless Methods
Freezing any row in Excel other than the top one is a complicated task. And there are several approaches. Considering everything, we decided to bring this guide to you. Here, we discuss the best 5 working methods for you to freeze 2 rows in Excel.
We emphasize learning through practice all the time. Especially when it comes to Excel, practicing makes you notice everything there is to a method and become a master of it. For this reason, we are also sharing the practice file with you. You can use it to follow the steps discussed in each method.
The visual demonstrations we have included in this guide were made using the practice file. Hence, it will be easier for you to follow the steps. Using the shared file to practice all the methods will also be extremely fruitful for you.
Now let’s get into the methods!
Method 1: Freezing 2 Rows Using Freeze Panes in Excel
For the first method, we show how to freeze 2 rows with the Freeze Panes option in Excel. It is simple and works most of the time. In earlier versions of Excel, it may not work properly. For that, other methods are discussed below as well.
In this demonstration, we are going to use the dataset shown here.
The goal is to freeze the first two rows so that the Subject header row (Row 2) stays visible while scrolling down to see other data. Let’s see how to do it.
1. Select the first cell of Row 3 (cell A3). You may also simply select the whole Row 3 by clicking on the row’s number tab.
The way this works is that the freezing option applies to the up (row) and left (column) directions of the active cell. Selecting an entire row or the first cell of a row has the same effect. It means that the freeze panes will be applied on the cells up (rows), but not on the left since there is no cell available to the left of the selection. When you select cell A1 or do not select any cell at all, the Freezing Panes will divide the rows and columns in your active view right at the center, both vertically and horizontally.
Next, click the View tab on the menu bar to open the View ribbon.
2. When the ribbon opens up, click on the Freeze Panes option inside the Window group. Then click the Freeze Panes option in the drop-down menu.
Notice that there are mentions of Freeze Panes twice. It’s because the first Freeze Panes is a button in the Window group under the View ribbon. Clicking this button opens a drop-down menu that contains another option called Freeze Panes. So, when Freeze Panes are mentioned in successive explanations, they are referring to two different options, one within the other.
3. Now you will see that the first two rows are frozen. You can notice the light dividing line as marked in the picture below (between two arrows).
You can scroll down to see that those two rows are still frozen.
4. When your work is done and you want to unfreeze the rows, simply go back and click the Freeze Panes option in the Window group under the View ribbon. Then click the Unfreeze Panes option in the drop-down menu.
5. Afterward, you will be taken back to the selection you made initially for freezing the top two rows (cell A3 or the whole Row 3). You will also notice that the thin dividing line marking the frozen two rows has disappeared.
Method 2: Freezing with the Split Option
In this method, we are demonstrating how to freeze 2 rows in Excel without “freezing” them. This is a workaround that does the job every single time. As mentioned in the previous method, the Freezing Panes option may not work properly sometimes in the earlier versions of Excel. This is where “freezing” rows with the Split option comes in handy.
Let us explain in a few words what the Split does in Excel. It separates your sheet into multiple panes that you can scroll individually. For instance, if you select Row 3 and then use the Split option, it will create two panes dividing them right above your selection.
At a first glance, the rows look only separated. If you scroll down your sheet below the thick separating line, you will find that you can scroll down and up in that part without interrupting the rows above the separator. But if you take your mouse pointer on the part above the separating line, select a cell in that area, and scroll down or up, you will find that those rows move as well!
However, for this method, as we intend to “freeze” two rows, we are going to divide right below Row 2 and scroll only the part beneath the separator. That way, we can keep the first two rows “frozen” and do our work.
Now let’s follow the steps below in the demonstration.
1. Select the first cell of Row 3. You may select the entire row as well by clicking on the row number. Then go to the View ribbon by clicking on that tab in the menu bar.
2. In the Window group under the View ribbon, you will find the Split button. Click on it while cell A3 is still selected.
3. Then and there, you will find that a thick separating line has appeared (marked between two arrows in the picture below). Now you can scroll down or up in the pane below the separator. Your top two rows will stay “frozen” and undisturbed.
4. If you want to remove the Split panes and bring everything back into one sheet, go to the View ribbon again and then click the Split button. It will remove the Split and take you back to your initial cell or row selection.
Simple and quick!
Method 3: Alt W F F and Alt W S Shortcut Keys Combination
In Excel, speed and error-free work are highly valued by any employer. The ability to use Excel shortcuts during an assigned task is what accounts for much of that speed and proficiency. The productivity gets multiplied because now you have more time on your hands to accomplish other things!
For this reason, we could not help discussing the shortcut options exclusively for freezing rows in Excel. We are taking the Access Key route for the shortcuts. The Alt key is known as the Access Key because it opens access to the ribbons and options. And we are going to use this key to open access to the needed options and complete our task in under a few seconds!
But first, let’s get familiar with how the Alt key is going to work in this case. Look at the picture below. The marked option is the menu bar. In its default state, it looks like the one here.
In that state, when you hit the Alt key, you will see letters accompanying each menu in the menu bar. And when you press the corresponding key for a letter on your keyboard, that ribbon will activate and show you further letters for each option. Then you have to press the corresponding letters or letter combinations for the options you would like to use.
This is how the Alt key opens the door to a great thousand of key combinations in Excel. It saves seconds every single time. And when you chain your every Alt key combination together, you get to save hundreds of minutes every day!
Now let’s see in steps how to use two Alt key combinations or shortcuts to freeze two rows in Excel!
3.1: The Alt W F F Combination
This Alt W F F key combination is an alternative to Method 1. It takes less time to accomplish the same goal of freezing the top two rows. Let’s see how it is done in steps.
1. Select Row 3 or cell A3 to freeze the top two rows.
2. Now press the Alt button on your keyboard to activate the access mode for the menu bar. You can see that the letter for the View ribbon is W. So now, press the W button on your keyboard.
3. At the press of W, the View ribbon will be activated and now you will find that all the options under that ribbon have further letters accompanying them. Press the F button for the Freeze Panes options to open the drop-down menu.
4. You will see that in the drop-down menu, the Freeze Panes option has an F letter again. Therefore, press the F button on your keyboard once more.
5. Now you will see that the top two rows are frozen. You are now able to scroll down the dataset without disturbing those two rows.
6. To unfreeze the rows, simply follow the same access key combinations of Alt W F F.
Afterward, you will find that the two frozen rows have been released and they are moving when you scroll down or up.
After using this Alt W F F shortcut or key combinations a few times for practice purposes or in your work, you will find that you can freeze two rows in under two seconds. That’s rapid for any Excel user!
3.2: The Alt W S Combination
The Alt W S key combination is an alternative to Method 2. This one also saves time as you use it to Split and separate your top two rows from the other part of the dataset. Everything about it is discussed in Method 2.
Now, let’s see in this demonstration how this Alt W S shortcut is going to save your time massively!
1. Begin by selecting cell A3 or the entire Row 3. You can know about the reasoning behind making this selection in Step 1 of Method 1.
2. Now press the Alt button on your keyboard. You will see that the W letter is visible right below the View tab in the menu bar. Therefore, press the W button.
3. Immediately, the View ribbon will be activated. Now you will find that each option is accompanying more letters. For the Split option, the letter S can be seen accompanying the option. So, press the S button on your keyboard.
4. At this moment, you will find your dataset to be Split right where you want it. Now you can scroll up and down without impacting the first two rows (unless you scroll within that range. Detailed explanation can be found in Method 2).
5. To Unsplit the sheet and re-attach your “frozen” rows, press the same key combination of Alt W S.
That was fast, wasn’t it?
Method 4: Freezing 2 Rows in the Middle of a Sheet
So far, we have discussed how to freeze 2 rows in Excel when they are the top two rows. Around 90 percent of the time, this would be the requirement. But that other 10 percent is where it gets tricky, and knowing how to handle it makes an expert Excel user stand out from a casual one. What if the requirement is to freeze two rows in the middle of a sheet? They are not at the top. So, what should an Excel user do in such a case?
Now, everybody will tell you it’s impossible to freeze multiple rows from a location other than the top in the exact way you can freeze the top two rows in a sheet. But we beg to differ.
Feeling confused still? Let’s take a look at the picture below.
A new dataset (List B) with a different set of headers begins in Row 106 in the picture shown above. In it, Rows 107 and 108 need to be frozen. Freezing them right where they are using the Freeze Panes option freezes the entire upper part of the sheet and thus it becomes stuck where it is currently. You can see it in the picture below.
Following the discussed methods, you can select the cell right below the headers and click the Freeze Panes option. Now see what happens.
Not just two rows (Rows 107 and 108 in this demonstration), but everything above the selected cell has been frozen. Only the rows below Row 108 can be scrolled. But this is not the solution. In this demonstration, the two rows from the middle of the sheet must be frozen at the top and not in the middle.
So now, let’s see how to freeze 2 rows in Excel when they are not the top two rows in a sheet!
1. Scroll the sheet up to the point where the two rows that you want to freeze are right below the column tabs.
For this demonstration, we are scrolling Row 107 up and placing it right below the column tab so that this becomes the top row visually.
2. When the top two rows are in place that are meant to be frozen, select the row beneath those two rows. Or you can simply click on the first cell of that row. In this demonstration shown below, it is Row 109 or cell A109. Either will work.
Then click on Freeze Panes in the drop-down menu of the Freeze Panes option in the Window group under the View ribbon. You may also use the Split option if you would like to.
3. Now you will see that those two rows are frozen and you can scroll down the list without having them move with the dataset.
4. If you want to unfreeze or unsplit your frozen rows, depending on which option you used between Freeze Panes and Split, just go back to the View ribbon and click on the same option again. Immediately you will see that the frozen rows have been released and you can scroll up and down the dataset again along with the headers.
This is how you can freeze two rows from the middle of a sheet!
Method 5: With Visual Basic for Applications (VBA)
In Excel, Visual Basic for Applications (VBA) and efficiency go hand in hand. When Excel users know how to use VBA, they become more productive because using VBA saves hours and energy on a tiresome job.
VBA also opens the pathway to many more opportunities in Excel that otherwise are not possible through the use of readily available options. For instance, in this VBA section of today’s guide, we will demonstrate how to freeze 2 rows in Excel not only in the sheet that you are in. You can also freeze two rows on multiple sheets at the same time. You can even freeze rows in all the sheets in your workbook in under a couple of seconds which is made possible only by VBA!
You can even modify a task to your liking. And it does not matter if you don’t know how to code in VBA. Even if you don’t understand it, you can simply copy-paste a code into the VBA editor and run the code to fulfill your task.
That’s what we are going to do here. And as a bonus, we will also demonstrate how you can clear all the frozen rows in your entire workbook without individually going to each sheet and clearing them from the View ribbon, thanks to VBA.
Time to learn some VBA magic then!
5.1: Freezing 2 Rows in Active Sheet
This method is an alternative to Methods 1-3 that we have discussed in this guide. If you would like to avoid following all the steps discussed in those three methods and want to freeze the top two rows in Excel by just copy-pasting some VBA codes, this one here is your friend! It is much easier and faster as well.
Let’s see the steps on how to do it.
1. First, click on the Developer tab in the menu bar to open the ribbon. Then in the Code group, click the Visual Basic option. You can also open the Visual Basic window by pressing the Alt + F11 keys.
If you don’t see the Developer tab in your Excel menu bar, we will show how you can add it to your Excel menu bar right after discussing all the VBA methods in this section.
2. When the Microsoft Visual Basic for Applications window pops open, click the Insert option in the menu bar, and then click the Module option from the drop-down menu.
3. You will now see a Module window where you can copy and paste code or write your own.
4. Now copy the code from below.
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
5. Paste the code into the Module editor box. Then click the green Run button as marked (2) in the picture below. You may also simply press the F5 button on your keyboard to run the code. Finally, close the VBA window.
6. You will be taken back to your active sheet where you will see that the first two rows have been frozen. You are now freely able to scroll down the sheet but the top two rows will stay in place.
Easy as pie!
5.2: Freezing 2 Rows in Multiple Sheets
With this method, things get more interesting with VBA. When you are required to freeze two rows on multiple sheets, one way is to go to each sheet individually, freeze two rows, and then move to the next sheet and repeat the process. But there is a way to avoid all that and execute the same task in under five seconds! The number of sheets does not matter.
To do it, we are going to make use of the Sheet Tab Bar as well (marked in the picture below).
Let’s go through the process and learn it, shall we?
1. First of all, select the sheets from the Sheet Tab Bar in Excel. Go to the first sheet that you want to select, then press and hold the Ctrl button on your keyboard. Now click on the other tabs of the sheets where you want to freeze the top two rows. Doing it will group those clicked tabs.
Next, click on the Visual Basic option in the Code group under the Developer ribbon. Alternatively, you can press the shortcut Alt + F11 buttons for the Visual Basic option.
2. Now click on the Insert menu and then click the Module option from the drop-down list. It will create and open a new Module editor box.
3. Now copy the code from below.
Dim Ws As Worksheet
4. In this step, paste the copied code into the Module editor box. Then click the Run button or press the F5 button to run the code. Finally, close the window to get back to the sheets.
5. Now you will see that the top two rows have been frozen in all the sheets that you selected before running the VBA code.
6. For the final step, you need to ungroup the sheets. To do it, right-click on one of the selected tabs, and then from the opened context menu, click the Ungroup Sheets option.
Just like that, you get to freeze two rows in multiple sheets with a single instance of a VBA code run!
5.3: Freezing 2 Rows in Entire Workbook
Now we move to the part where we demonstrate how you can freeze the top two rows of all the sheets in your workbook. This is a pretty familiar situation for any Excel user at work. You can go to each sheet and do it one by one. But it is neither productive nor efficient because of the time it will take.
In such cases, VBA offers the best solution for you. With a few lines of code, you can freeze the top two rows of all the sheets that you have in your workbook. Even if you have hundreds of sheets, VBA will let you do it in under five seconds! How awesome is that?
Let’s go through the process of how to freeze 2 rows in Excel for an entire workbook!
1. As always, go to the Developer ribbon first. Then in the Code group, select Visual Basic. You can also perform the same action by pressing Alt + F11.
2. In the MS VBA window that just opened up, click to expand the Insert menu first. Then from the drop-down menu, click the Module option to create and open a new Module editor box. This is where you will write or copy-paste the code.
3. Copy the VBA code for freezing 2 rows in Excel given below.
Dim Ws As Worksheet
4. Now go back to the Module editor box, and paste the copied code. Then either click the green Run button as marked (2) in the picture or simply press the F5 button to run the code. Then close the VBA window.
5. You will be taken back to your workbook where you will find that the top two rows in every sheet in your workbook have been frozen!
Now you can take a lengthy coffee break with all the minutes or hours of work that you have saved by using this method!
5.4: Using the Split to Freeze 2 Rows in Entire Workbook
In the first two methods of this guide, we also discussed how there can be times when the Freezing Panes option may not work properly for various reasons. One workaround was to use the Split option. But the best substitute is this VBA method which utilizes both the Split and Freeze Panes options to offer the most surefire way to freeze two rows in Excel.
If nothing else works, this will most definitely work. So now, let’s see in steps how we can get the job done most effectively!
1. Click the Visual Basic option in the Code group under the Developer ribbon. Or you can press the Alt + F11 shortcut buttons for the same operation. It will open the MS VBA window.
2. Now in the VBA window, click the Insert menu first and then click the Module option from the drop-down list. It will open the Module code editor box.
3. Now copy the code from below and then go back to the VBA window.
Dim Ws As Worksheet
4. Paste the copied code inside the Module box. Then press the Run button marked (2) in the picture below to run the code. You may also press the F5 button for the same action. Finally, close the VBA window.
5. You will be taken back to your workbook. You will see that the first two rows are now frozen in all the sheets in your workbook.
5.5: Unfreezing and Unsplitting the Frozen Rows
This is not about freezing the rows, but the exact opposite. However, VBA also rocks here. Just like those instances where you have to freeze rows in multiple sheets and VBA makes your work easier, the unfreezing process follows the same approach. You can unfreeze all the frozen rows in your entire workbook in under a few seconds, thanks to VBA yet again!
The steps are demonstrated below.
1. Go to the Developer ribbon to open the Visual Basic option from the Code group. Or simply press the Alt + F11 buttons if you want to avoid going through the menu ribbons.
2. As always, click on the Insert menu to open the drop-down menu and then click the Module option from there.
3. Now copy the VBA code given below.
Dim Ws As Worksheet
4. Paste the copied code inside the Module code editor box. Then click on the Run button or simply press the F5 button on your keyboard. Next, close the VBA window to complete the process.
5. Now you will find that all the frozen rows in your workbook have been released, and you can scroll up or down freely in any of your sheets.
This method works perfectly in any situation. Even if you have not used the VBA option to freeze or split rows, you can unfreeze them with this VBA method. It un-splits and unfreezes the rows at the same time!
Enabling the Developer Option
If you cannot find the Developer tab in your Excel menu bar, follow the two steps below to add it to the menu.
1. From the File menu in your Excel window, click on Options.
2. A new window titled Excel Options will open up. From there, click on the Customize Ribbon tab in the left column. The Customize the Ribbon options will then appear. Now locate the Developer option in the right column. By default, it is unchecked. To add it to your Excel menu bar, be sure to check the Developer option. Finally, click OK to complete the process.
From now on, you will find the Developer tab in your Excel menu bar.
Frequently Asked Questions (FAQs)
Some questions are asked by many Excel users when they try to freeze 2 rows in Excel. To make this guide complete, we gathered a few of those questions and would like to provide answers to them for you.
Q: Why can’t I freeze rows in my Excel worksheet?
A: You may fail to freeze rows for primarily three different reasons: your current workbook view is not set to Normal; you already have a frozen row in your sheet; or there are frozen panes in your workbook somewhere that are interfering with your active sheet. To solve the problem, follow each of the solutions given below.
- Go to the View ribbon and see if Normal view is active in the Workbook Views group.
- Make sure that there are no frozen panes in your current sheet already. Use Method 5.5 (VBA) to release existing frozen panes.
- Clear all the frozen panes within your workbook by using Method 5.5 (VBA) from this guide.
Q: Why doesn’t Excel allow me to split rows?
A: You will be unable to use the Split option if the sheet already has a split or freeze pane active. Make sure to clean up the existing splits and frozen panes from the sheet. You may follow Method 5.5 for easier removal of existing splits and frozen panes.
Q: How can I freeze two different rows in Excel?
A: Unfortunately, there is still no way to freeze two different rows in Excel at this moment. You can only freeze one row or a range of rows. And there can be only one instance of freezing or splitting in an Excel sheet. Therefore, freezing rows from different locations within the same sheet is not possible.
We have reached the concluding part of our guide on how to freeze 2 rows in Excel. We have explained the steps and shared visual demonstrations of how you can freeze two rows by using five different techniques. As an active user of the Excel program, you should practice and master all the methods discussed in this guide.
However, if you are short on time, we can share some ideas on what might be the best approach for you in general. If you are dealing with only a single sheet where two rows have to be frozen, Method 1 and Method 2 are the most straightforward options. Method 3 offers alternatives to the first two methods. This method should be your go-to method if you are at least an intermediate-level user. Method 4 is a special but extremely important case. It demonstrates how you can freeze two rows from the middle of your sheet.
Method 5 is all about Visual Basic for Applications (VBA) which is most appropriate for expert-level users of Excel. However, any Excel user can follow all the techniques in Method 5 because we have shared the needed VBA codes with you. This method may seem intimidating at first, but it saves a huge amount of your time and does a lot of work in the quickest way possible.
Now it’s up to you to decide which method you want to go with. For their various applications, we highly recommend that you practice each method and use the most fitting one depending on your situation. Give all the methods a try, and then pick your weapon!