The Excel freeze frame feature allows you to keep certain rows or columns visible as you scroll through your worksheet.
This feature is useful when you have a large dataset, and you want to keep the column or row labels in view while you scroll through the rest of the data.
By freezing the rows or columns, you can also make it easier to navigate through the worksheet, as you can always see the labels that help you identify the data in each column or row.
Here we will use a sample dataset for easy understanding. We have a weekly sales amount history of a company with their product names.
It is a moderately large dataset. So, we will use the Excel freeze frame feature to freeze rows and columns to easily handle the data. Let’s get to know 6 quick tricks to freeze a frame in Excel.
1. Using “Freeze Panes” Feature to Freeze Frame in Excel
Excel has some built-in features, Freeze Panes is one of them. The Freeze Panes feature allows you to freeze specific rows or columns in your worksheet.
1.1. Freezing Top Row
We can use this feature to freeze only the top row of the worksheet.
- Go to the View tab >> click drop-down of Freeze Panes >> select Freeze Top Row.
- You will see that Excel has frozen the top row separated by a gray-colored line.
1.2. Freezing Multiple Rows
We will use the same dataset to freeze multiple rows. This method is kind of similar to the previous one.
- Select a row (i.e. row 5) above which you want to freeze multiple rows.
- Then go to the View tab >> Freeze Panes drop-down >> Freeze Panes.
- We will see a gray-colored line after row 4 and all the rows above 5 are frozen.
1.3 Freezing First Column
Likewise, in the previous methods, we can also lock the first column in Excel. Let’s see the procedure with the same dataset.
- Go to View tab >> Freeze Panes drop-down >> Freeze First Column.
- If we scroll the mouse from the left to right direction, we can see the frozen column.
1.4 Freezing Multiple Columns
Using the above dataset, we are going to freeze multiple columns (A, B, C, D).
- Select column E >> go to View tab >> Freeze Panes >> Freeze Panes.
- All the columns before column E are frozen.
1.5 Freezing Multiple Rows & Columns Together
Till now, we discussed freezing only rows or columns. If we want to freeze both of them then we need to follow the below instructions. The dataset is the same here as well. We will freeze columns A, B, C & rows 1, 2, 3, 4.
- Select cell D5 >> go to View tab >> Freeze Panes >> Freeze Panes.
- We will see the required columns & rows are frozen.
2. Applying Excel “Split” Feature to Lock Frame
The Split feature in Excel allows you to divide the worksheet window into multiple panes, each with its own scroll bars, allowing you to view different parts of the same worksheet simultaneously. We can use the Split feature in Excel to freeze the frame. We are going to lock columns A, B & C as well as rows 1, 2, 3 & 4.
- Select cell D5 >> View tab >> select Split feature from Window group.
- We can see that the worksheet is split. The columns & rows both are locked as well.
3. Customizing Excel Quick Access Toolbar to Freeze Horizontal and Vertical Frame
For locking rows & columns, we can customize Quick Access Toolbar. This feature saves a lot of time during monitoring any dataset. Imagine we have the same dataset as before. Now we are going to see how to use this magic freeze button to lock both rows & columns.
- Click the Customized Quick Access Toolbar drop-down >> select the More Commands option.
- The Excel Options window will pop up.
- Go to the Quick Access Toolbar tab >> select the Freeze Panes option >> click Add button.
- Click OK.
- You can see that the Freeze Panes drop-down is added to the Quick Access Toolbar.
- Click on the Freeze Panes drop-down that we added in the Quick Access Toolbar.
- Select Freeze Panes.
- Finally, we can see the frozen frame in the worksheet.
4. Freezing Frame in Excel with Keyboard Shortcut
Sometimes, keyboard shortcuts make our work easier and save searching time. Let’s say we have the same dataset of the weekly sales amount of the products. So we will lock columns A, B,C & rows 1, 2, 3, 4 by using the keyboard shortcut.
- Select Cell D5.
- Next press the ALT & W keys one by one. This will open the View tab like the below screenshot.
- Hit the F key. This will open the Freeze Panes drop-down.
- Again press the F key.
- Finally, this will freeze the required frames.
5. Applying Excel VBA to Freeze Rows & Columns
By applying the Excel VBA code, we can easily lock the frame wherever we want. Let’s assume, we have the same dataset as before. Now we are going to insert a VBA code to freeze columns A, B, C & rows 1, 2, 3, 4, 5.
- Press ALT+F11 to open the Visual Basic Editor window.
- On the window, click Insert and select Module.
- Insert your code in the appeared Module window.
Sub FreezeFrame() ActiveSheet.Range("D5").Select ActiveWindow.FreezePanes = True End Sub
- Click Run to execute the code.
- In the end, if we do the scrolling, we can see that the required frames are frozen.
6. Using Excel Table to Freeze Frame Quickly
Excel Table is also an important feature. This makes the dataset dynamic. In this method, we are going to use a table in the previous dataset and lock the top row.
- Select a random cell from the dataset >> go to the Insert tab >> select the Table option.
- A Create Table message box pops up. Make sure the data placement is correct. Also, put a tick mark on the ‘My table has headers’ option.
- Click on OK.
You can see that a table is created.
- After that, select any cell (i.e. D6) inside the table.
- Now scroll down the worksheet and we will see that the top row is frozen.
Related Content: Excel Freeze Panes Not Working (5 Causes with Fixes)
How to Unfreeze Frames in Excel
From the above methods, we came to know about how to freeze the frame. What if we need to unfreeze the frame?
- Click the Freeze Panes drop-down from the View tab of the worksheet >> select Unfreeze Panes option.
This will unlock the frame of the Excel worksheet.
Excel Freeze Frame Not Working
There may be several reasons why the freeze frame is not working in Excel.
- Workbook views set to Page Layout: 3 views are available in the Workbook Views section. If a user accidentally selects Page Layout view, the Freeze Panes feature won’t work in the worksheet. The user should select the default Normal view to avoid this.
- Workbook protection is enabled: For business purposes, Excel workbook is sometimes protected for security issues. In such cases, the freeze frame feature does not work. You have to unprotect the sheet from the Review
- Sometimes worksheet is even protected by earlier versions of Excel for which freezing doesn’t work.
Frequently Asked Questions
1. Does freezing frames impact the size or formatting of my Excel sheet?
Freezing frames in Excel should not impact the size or formatting of your sheet. The freeze frame feature only affects the view of your sheet on your screen. It does not change the actual layout or structure of your sheet or affect any of the data or formulas contained within it.
2. Can I still edit cells when frames are frozen?
Freezing panes or frames allows you to keep certain rows or columns visible while scrolling through the rest of the spreadsheet. However, it does not restrict your ability to edit cells.
3. How do I know if my frames are frozen or not?
If frames are frozen, the row and/or column labels will have a solid line separating the frozen cells from the rest of the spreadsheet.
Takeaways from this Article
- The freeze frame feature helps to identify data in row and column and make it easy to handle the worksheet.
- You can freeze horizontal and vertical frames separately or both at the same time.
- Excel can freeze both single and multiple rows or columns.
- To determine if frames are frozen, look for a solid line separating the frozen cells from the rest of the spreadsheet in the row and column labels, or check the “View” tab to see if the “Freeze Panes” option is grayed out.
Things to Remember
We have to remember that, we cannot freeze only any specified row or column in the middle of the worksheet. Moreover, it could be possible that the Freeze Panes button is showing unavailable. That means it won’t work. To avoid this problem-
- We need to make sure that the worksheet is not in a protected mood.
- The cell is not in an editing mood. Hit ESC or ENTER to avoid this editing mood.
You can download the practice book from the link below.
Excel freeze frame feature can be a useful tool when working with large spreadsheets. It allows you to keep important rows or columns in view while scrolling through the rest of the sheet. By following a few simple steps, you can easily freeze frames in Excel and unfreeze them as needed. Even with frames frozen, you can still edit cells in Excel. Understanding how to freeze frames in Excel can help you work more efficiently and effectively with your data.