Let’s say that we have a large table, containing 45 rows. We want to be able to see the first two rows that contain the information about our data set.
The table describes the price of the ordered items by each region and it looks as in the picture below:
As I said, we have 45 rows in this table. We might add more of the data in the future, meaning that it will be only harder for us to track down the explanations for our data.
For example, if we simply scroll down to the end of our table, we will see this:
We would like to keep our first rows intact when we scroll, to see the headers of these figures and the meaning behind these numbers.
For this, we use a very useful option in Excel- Freeze Panes.
This option is very easy to find in Excel. It is located in the ribbon, on the View tab.
The Dropdown menu of Freeze Panes has three options:
- Freeze Panes– Keeps rows and columns visible while the rest of the worksheet scrolls (based on current selection).
- Freeze Top Row– Freezes only the top row of our Excel file.
- Freeze First Column– Freezez only the first column in our Excel file.
Last two option seems logical. But what if we want to freeze more than just one column, or one row? In this case, we have to use option number one.
Freeze Two Rows in Excel
To freeze (or to lock) multiple rows (starting with row 1), you have to select the row below the last row you want frozen, and then use the option Freeze Panes.
In our example, since we want the first two rows locked, we have to select row number 3 (row below the one we want to be frozen) and then go to View– Freeze Panes– Freeze Panes.
When we do so, you will notice that we will get a bolded line at the end of our second row.
As seen from the picture below, we have locked our first two rows, so when we scroll down to row number 40, we can still see the headers of our data.
To unfreeze your rows, you just have to go to the View tab again. You will now have Unfreeze Panes option instead of Freeze Panes. Just click on it and your rows will not be locked anymore.
Freeze Two Rows in Excel with VBA
Although it is quite simple to freeze your rows in Excel, it can always come in handy to know how to freeze the rows with VBA as well.
In the simple VBA code presented below:
Sub FreezingPanes() Rows("3:3").Select ActiveWindow.FreezePanes = True End Sub
We can see that we do the same thing as in the example above. In this case, we have to select the third row (since everything above it will be frozen) so we do that in the first line of the code.
After that we just type ActiveWindow.FreezePanes = True.
That will do all the trick for us. To unfreeze the panes, we just have to change the statement to:
ActiveWindow.FreezePanes = False