How to Freeze Selected Panes in Excel (4 Suitable Examples)

Freezing panes is an excellent feature Excel offers that keeps certain rows or columns visible while scrolling through the spreadsheet. When a dataset is large and you want to keep certain parts of the spreadsheet visible while reading through other data, this really comes in handy. In this article, we will discuss how to freeze selected panes in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

Freeze Selected Panes.xlsm

How to Freeze Selected Panes in Excel: 4 Suitable Examples

Freezing options are available in the View tab of the Excel ribbon. By default, you can freeze the first row or column in Excel. There are other ways to freeze selected panes in Excel too. We will go through them in their own subsections.

For the demonstration, we are using the following dataset. It contains a list of employees with their IDs and their particulars. We will freeze different portions, depending on the method so that the parts get frozen while scrolling.

1. Freeze Either Selected Rows or Columns Individually in Excel

As we mentioned earlier, Excel provides direct features to freeze the first row and column of a spreadsheet.

Freeze Top Row

To freeze the topmost row in the spreadsheet follow these steps.

Go to the View tab and select Freeze Panes from the Window group.
From the drop-down menu, select Freeze Top Row.

As you have done that, you will notice a grey line appearing below the first row.

Now you can scroll down through your spreadsheet. You may notice that the first row is always there no matter at what point you are in the spreadsheet.

Freeze First Column

To freeze the first column instead, you need to follow these steps.

Go to the View tab and from the Window group, select Freeze Panes.
Now select Freeze First Column from the drop-down menu.

Once you have done that, you will notice the same grey line emerging on the sheet. But this time, it is appearing vertically on the right side of the first column.

If you scroll to the right now, the first column will always be there.

Freeze Multiple Rows

Previously, we have seen Excel’s feature freeze only the first row. But what if you want multiple rows frozen? For example, we want to freeze both headers and the dataset title. That would be the first four rows in our spreadsheet. To freeze these selected panes in Excel follow these steps.

Select the fifth row (the row after the freezing should end) by clicking on the row index on the left of the spreadsheet.
Then go to the View tab and select Freeze Panes from the Window group.
From the drop-down menu, select Freeze Panes.

Now the grey line will appear horizontally after the fourth row. Scroll down the sheet and you will see the first four rows are frozen.

Freeze Multiple Columns

In a similar fashion, we may want to freeze a number of columns on the spreadsheet depending on the dataset. For example, we may want to freeze the “Employee ID” and “Full Name” columns. To freeze these selected panes in Excel follow these steps.

First, select column D (the first one that should not be frozen).
Then go to the View tab and select Freeze Panes from the Window group.
From the drop-down menu, select Freeze Panes.

Now the grey line will appear vertically after the C If you scroll the sheet to the right, you will see the first three columns are not moving with it.

2. Freeze Selected Columns and Rows at the Same Time

In the previous section, we discussed how to freeze either rows or columns. But it is more practical to freeze some rows and columns in our everyday Excel usage. To demonstrate this, we are going to freeze four rows and two columns in the sheet. To freeze these selected panes follow these steps.

Select the cell below and on the right of the freeze rows and columns. In this case, it would be cell C5.
Then select Freeze Panes from the Window group of the View tab.
From the drop-down menu, select Freeze Panes.

You may notice a grey line appearing horizontally after the fourth row and vertically after the second column. If you scroll up and down or left and right, these panes won’t move.

Freeze Panes Button for Quick Access Toolbar

You may have noticed that we are using the Freeze Panes option from the drop-down for selected panes in the Excel spreadsheet again and again. You might not want to go through the ribbon every time. Instead, you can have the command in the Quick Access Toolbar available on the top of the ribbon in Office applications. To add the feature in the toolbar, follow these steps.

Right-click on the Quick Access Toolbar on top of the Excel window and select More Commands from the context menu. You can also access this by clicking on the downward-facing arrow available in the toolbar.

Now select the Quick Access Toolbar tab on the Excel Options box that opened up.
Under the Choose commands from option, select Commands Not in the Ribbon.
On the left of the box, select the Freeze Panes option and select Add>>.

After clicking on OK, you will find the Freeze Panes button available in the Quick Access Toolbar.
Now select the cell below and to the right of the pane you want to freeze and click on the button to freeze selected panes in the Excel spreadsheet.

3. Freeze Selected Panes Using Split Feature and Get Frozen Panes Twice

Excel offers another feature that can freeze selected panes- Split. It is quick and easy. But you will see the frozen panes twice if you scroll up or to the left. Sometimes you may not want that. However, it still freezes selected panes. We are going to freeze (or split) the column headers and the “Employee ID” column. To see how this method works, follow these steps.

Select the cell below and right of the pane you want to freeze. In this case, it is cell C5.
Then go to the View tab.
Select Split from the Window group.

You will see the grey lines appearing on the top and left of the cell we selected. Now if you scroll down or to the right, this will freeze selected panes in the Excel spreadsheet.

However, if you scroll up or to the left, you may see the panes appearing twice.

If you don’t prefer this view to work with, avoid this method.

4. Freeze Headers by Converting Dataset into Excel Table

Another lowkey freezing method you can apply is to convert the dataset into a table. This will only freeze the headers of the dataset. It will show on the column indexes instead. However, you can still view them as you scroll down. We can’t freeze any columns with this method, only the first row of the table if it is a header row.

To convert a dataset into a table, select the dataset and go to the Insert tab on the ribbon.
Then select Table from the Tables group.

A Create Table box will open up. Make sure the correct range is in the field and check the My table has headers option.

As a result, the dataset will now convert into an Excel table with filters on the headers.

Now if you scroll down while selecting a cell on the table, you will see the frozen panes on the column index.

Note: This will work as long as you have selected a cell from the table. If you select a cell outside the table and scroll down, you may see the natural column index instead.

Converting Table from Power Query

You can also achieve the same result using Power Query too. Loading the data from Power Query will convert the data into a table. And as you scroll down, you will see the headers in the column index.

To load a range in Power Query, select all cells in the dataset.
Go to the Data tab on the ribbon and select From Table/Range from the Get & Transform Data group.

Check the particulars in the following table and click on OK.

As a result, the Power Query window will open up.

A table will appear on a new sheet with the filters on the headers just like how we find them on Excel tables.

Now you can scroll down and see the headers are frozen on the column index as long as a cell is selected in the table.

Note: you can convert a range into a table too with the Ctrl+T shortcut after selecting the range.

5. Using VBA Code to Freeze Selected Panes Quickly

Visual Basic for Application (VBA) is a programming language available in Microsoft Office applications. We can use it for simple tasks such as selecting a cell to automate repetitive complex tasks.

We can use them to freeze selected panes too. The result is the same as freezing panes, so the usage of this method depends entirely on preference.

The property for freezing panes in VBA is the .freezepanes. However, you need the Developer tab to show on the ribbon first. If you don’t have one, you can check out how to display the Developer tab on the ribbon.

Go to the Developer tab and select Visual Basic from the Code group of the ribbon.

The VBA window will open up. Select the Insert tab here and click Module from the drop-down.

In the module, insert the following code.

Sub freezepanes()

Range("C5").Select
ActiveWindow.freezepanes = True

End Sub

Now press F5 to run the code. It will freeze the selected panes (assigned with cell C5) from the code in the current Excel spreadsheet.

This is how we can freeze selected panes using VBA in Excel.

Note: To freeze other panes, you can change the cell inside the range of the code to the one below and right of the pane.

How to Unfreeze Selected Panes in Excel

When reading the data and analyzing you may want to remove the panes from the spreadsheet. Or you may have frozen them for your viewing purposes and you want to share the original one with others. Excel has an unfreezing feature available for that purpose too. No matter how you may have frozen the selected panes (including VBA) this method will undo that.

First, select the sheet where you want to unfreeze and go to the View tab on the ribbon.
Then select Freeze Panes. You can find it in the Window group.
Select Unfreeze Panes from the drop-down menu.

As a result, this will unfreeze all panes in the sheet.

Note: Pressing Ctrl+Z will not undo the Freeze Panes or Unfreeze Panes commands.

Freeze Panes Not Working in Excel

Sometimes you may find the Freeze Panes command greyed out on the ribbon. This can happen for the following reasons.

You are in cell editing mode: This is when you enter values in the cell. Any key you press on your keyboard at this point enters a value in the cell. Press Esc to exit this and the Freeze Panes command should work again.
Your spreadsheet is protected: Spreadsheet owners sometimes protect their sheets before sharing to avoid further editing of the sheet. In this case, you can’t edit or freeze cells. To unprotect them, go to the Review tab > select Unprotect Sheet.
Multiple Windows: Sometimes the Freeze Panes command behaves unnaturally because there are multiple windows of Excel running. In that case, close additional windows and try freezing the panes again.

Frequently Asked Questions

What happens if I delete or modify the frozen rows or columns?

If you have multiple rows or columns frozen, the number of frozen ones will decrease by one. But if you have one row or column in the pane, the 2nd row or column will shift to the frozen pane.

Can I freeze panes in a selected range, instead of the entire worksheet?

You can freeze panes from the start of the row (or column) to any of the preferred ones. However, you can not freeze some rows or columns in the middle and scroll both sides as in the current version of Excel.

Can I freeze panes in Excel without using the ribbon or menu options?

You can use the Quick Access Toolbar to add a freezing option in it and use. Or you can use VBA. Otherwise, you need to visit the ribbon.

Things to Remember

Pressing Ctrl+Home (which usually takes you to the first cell of the sheet) takes you to the first cell of the unfrozen row in a spreadsheet where the Freeze Panes option was used.
If you press Ctrl+End you will get to the end cell of the dataset for both sheets with frozen and unfrozen panes.
Always select the cell below and right of the pane you want to freeze. Consider it as the intersection of the first unfrozen column and row.
Converting a range into a table will only freeze the headers.
Ctrl+Z will not undo the freezing/unfreezing operation.
The freezable rows/columns always start from the first to the previous of what you select.

Conclusion

That concludes our discussion on how to freeze selected panes in Excel. We can use the Freeze Panes and Split feature for this, convert a range into a table, and use VBA to freeze. Hopefully, you have found this guide helpful and effective. I hope you can freeze your selected panes in Excel quickly now. If you have any questions or suggestions, let us know in the comments below.

Get FREE Advanced Excel Exercises with Solutions!