As you scroll horizontally across a worksheet with many columns, the columns at the beginning of the worksheet scroll out of view.
Usually, the first column (or the first few columns) has the headers for each row.
And when you scroll to the right of your worksheet, it would be helpful if the headers in the column were visible.
When they are visible all the time, you do not have to keep scrolling back to see what the values further across the worksheet mean. This saves effort and time.
This is where the ability to freeze columns comes in handy. This tutorial shows you four methods of how to freeze multiple columns in Excel.
This Tutorial Covers:
ToggleMethod #1: Freeze Multiple Columns Using the Freeze Panes Option
The following example dataset extends to column V and is not fully viewable on a single screen.
If we scroll to the right towards the last column of the dataset, the columns at the beginning of the worksheet move out of view.
We want to change Excel’s default behavior such that as we scroll the worksheet to the right, the columns A, B, and C remain visible.
Below are the steps to freeze multiple columns using the Freeze Pane option in the ribbon:
- Select column D, which is immediately on the right of columns A, B, and C.
- Click the View tab, in the Window group, open the Freeze Panes option and click Freeze Panes.
- Click anywhere in the worksheet to deselect column D.
Notice the grey vertical line running down the worksheet on the right border of column D. This indicates that all the columns on the left of the line are frozen.
You can now scroll the worksheet to the furthest extent to the right, and columns A, B, and C will remain visible.
In the above screenshot, you can see that I have scrolled to the right, and I have columns T, U, and V visible, while columns A, B, and C have been frozen and remain visible all the time.
Unfreeze the Columns
In case you want to unfreeze the columns, open the View tab, open the Freeze Panes options in the Window group, and click Unfreeze Panes.
Method #2: Keyboard Shortcut to Freeze Multiple Columns
Below is the keyboard shortcut to freeze multiple columns in Excel:
ALT + W + F + F
Below I have a large dataset that is not viewable on a single screen, and I will have to scroll to the right if I want to view data in far-off columns.
And as soon as I scroll to the right, the columns at the beginning of the worksheet are out of my view.
Here are the steps to use the keyboard shortcut to freeze the first three columns:
- Select any cell in column D.
- Press and release the following keyboard keys one after the other: Alt + W + F + F.
The first three columns are frozen.
Note: To unfreeze the columns, use the same shortcut Alt + W + F + F again. It will unfreeze any row or columns that have been freezed
Method #3: Freeze Multiple Columns Using VBA
We can use Excel VBA to freeze multiple columns in Excel
Below is a dataset where I want to freeze the first three columns
Here is the VBA code to freeze the first three columns
'Code developed by Sumit Bansal from https://trumpexcel.com
Sub FreezeFirstThreeColumns()
Columns("D:D").Select
ActiveWindow.FreezePanes = True
End Sub
Below are the steps to use the above VBA code in your workbook:
- Click the tab of the worksheet containing the dataset to activate the worksheet.
- Open the Developer tab and click the Visual Basic button in the Code group.
- In the Visual Basic Editor, open the Insert menu and choose the Module item.
- Copy the above VBA code and paste it in the Macro code window
- Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the procedure and press F5 to run the code.
- Click the View Microsoft Excel button on the toolbar to switch to the active worksheet.
Notice that column D is selected. Click anywhere in the worksheet to deselect the column. The first three columns of the worksheet are frozen.
In the above code, I wanted to freeze the first three columns, so I used Columns(“D:D”).Select in my code. In case you want to freeze the first two columns, you can use Columns(“C:C”).Select
You can also modify the above code to freeze one to multiple rows as well (or both rows and columns)
In case you want to freeze multiple columns in all the worksheets in your workbook, you can use the below code:
'Code developed by Sumit Bansal from https://trumpexcel.com
Sub FreezeColumnsinAllSheets()
For Each ws In Worksheets
ws.Activate
Columns("D:D").Select
ActiveWindow.FreezePanes = True
Next ws
End Sub
Note: VBA method to freeze rows or columns in Excel is suitable only when you have to do this quite often. For example, if you want to freeze columns in multiple worksheets for all the worksheets in your workbook, then the VBA method would be helpful. But if you want to do this in one or two worksheets only, then you are better off using the keyboard shortcut or the Freeze Panes options in the ribbon.
Method #4: Keep Multiple Columns Visible Using the Split Option
Let me show you an innovative way to lock the rows or columns in their place while you can scroll and look at data that’s far off in your worksheet.
Excel’s Split option allows us to divide the window into different panes that scroll separately.
We can then scroll the worksheet in the second pane, and the columns we want to remain visible will remain visible in the first pane.
Below is a dataset that extends till column V, and I want to have the first three columns always visible on the screen when I scroll to the right.
In this case, we can split the Excel screen into two panes, where the first pane has the columns I want to freeze in its place, while the second pane can be used to scroll the data
Below are the steps to do this:
- Select column D.
- Click the View tab and click the Split command in the Window group. The Split button becomes dark grey indicating that it is active.
- Click anywhere in the worksheet to deselect column D.
Notice the grey split line between the two panes:
The worksheet has been split into two panes after column C, and each pane has its own scroll area.
You can scroll the worksheet in the second pane to the last column of the dataset, as columns A, B, and C remains visible in the first pane.
To remove the split
If you want to remove the split, click on the Split button to deactivate it.
The split line is removed from the worksheet, and the two panes collapse into one. The Split button becomes transparent, indicating that it is inactive.
In this tutorial, we have learned how to freeze columns to keep them visible while we scroll to another area of the worksheet.
This way, we do not have to keep scrolling back to the row headers to see what a value means. This saves a lot of time and effort.
This tutorial has shown four techniques for freezing multiple columns in Excel. The techniques involve using the Freeze Panes command, the Split command, the keyboard shortcut, and Excel VBA.
Other Excel articles you may also like:
- Excel Freeze Panes: Use it to Lock Row/Column Headers
- How to Zoom-in and Zoom-Out in Excel (Shortcuts)
- How to Lock Row Height & Column Width in Excel
- Excel AUTOFIT: Make Rows/Columns Fit the Text Automatically
- 5 Ways to Insert New Columns in Excel (including Shortcut & VBA)
- How to Group Columns in Excel?