How to apply freeze / unfreeze panes to multiple worksheets at once?
When working with large Excel workbooks containing dozens or even hundreds of worksheets, you may frequently need to keep certain rows or columns visible by freezing panes. However, Excel's default Freeze Panes function only operates on the active worksheet, making it time-consuming to repeat this action on every sheet manually. If you need to consistently freeze (or later unfreeze) the same rows or columns across all your worksheets, is there a way to apply this setting to every worksheet at once—saving you effort and reducing the chance of missing a sheet?
Apply Freeze Panes to all worksheets at once with VBA code
Apply Unfreeze Panes to all worksheets at once with VBA code
Apply Freeze / Unfreeze Panes to all worksheets at once by one click with Kutools for Excel
Apply Freeze Panes to all worksheets at once with VBA code
If you wish to freeze panes at the same cell location across every worksheet in your workbook, VBA provides an efficient solution to automate this process. It can help maintain a uniform view for all sheets, such as keeping header rows or key columns visible throughout large workbooks. Note that this method requires you to select the cell where you want to apply the freeze in each worksheet, as VBA will freeze based on the active cell within each one.
1. If you want to freeze all worksheets in the same position, select a cell that you want to freeze in the worksheet, and then hold Shift key to select all sheet tabs.
2. Press ALT + F11 to open the Microsoft Visual Basic for Applications window.
3. In the VBA editor window, click Insert > Module, then copy and paste the following VBA code into the module window:
VBA code: Freeze all worksheets at once in Excel
Sub Freeze()
'Updateby Extendoffice
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In Application.ActiveWorkbook.Worksheets
Ws.Activate
With Application.ActiveWindow
.FreezePanes = True
End With
Next
Application.ScreenUpdating = True
End Sub
4. To execute, press F5 or click the Run button. This will apply the freeze panes in the position of the active cell for all worksheets.
Note: If you have not selected all worksheets and the active cell differs between sheets, freeze pane locations may vary from sheet to sheet. To ensure uniformity, always align the active cell before running the code.
Apply Unfreeze Panes to all worksheets at once with VBA code
When several worksheets have frozen panes applied and you wish to remove this setting from all sheets simultaneously, a VBA solution can greatly streamline the process. Instead of manually unfreezing panes sheet by sheet, the following macro quickly unfreezes all worksheets at once.
1. Open the workbook containing frozen panes across multiple worksheets.
2. Press ALT + F11 to launch the Microsoft Visual Basic for Applications editor.
3. Click Insert > Module, then paste the code below into the module window:
VBA code: Unfreeze all worksheets at once in Excel
Sub UnFreeze()
'Updateby Extendoffice
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In Application.ActiveWorkbook.Worksheets
Ws.Activate
With Application.ActiveWindow
.FreezePanes = False
End With
Next
Application.ScreenUpdating = True
End Sub
4. Press F5 or click the Run button to execute. All worksheets with frozen panes will have them removed instantly.
Apply Freeze / Unfreeze Panes to all worksheets at once by one click with Kutools for Excel
If you prefer not to use VBA or are looking for a simpler, code-free method, Kutools for Excel provides an effective way to apply Freeze or Unfreeze Panes across all worksheets using a straightforward menu. This feature is ideal for users who want a quick, reliable solution with minimal steps and no need for macro setup. The Freeze panes multiple worksheets and Unfreeze panes multiple worksheets utilities work seamlessly to set up or remove frozen pane positions in every worksheet with a single action. This saves considerable time in large workbooks and lowers the chance of inconsistency between sheets.
After installing Kutools for Excel, please follow these steps:
1. Choose the cell at which you'd like to freeze panes in all worksheets (for example, select cell B2 if you want to keep row1 and column A visible). This position will be applied uniformly to each worksheet. See screenshot:
2. On the Excel Ribbon, click Kutools Plus > Worksheet > Freeze panes multiple worksheets. See screenshot:
3. All worksheets will immediately have freeze panes set in the same designated cell position (e.g., B2), so your view remains consistent across the entire workbook.
Note: To remove freeze panes from all worksheets in one operation, select Kutools Plus > Worksheet > Unfreeze panes multiple worksheets. This provides flexibility to toggle the view as needed when working with large or complex data sets. The feature is particularly helpful during template preparation, board meetings, or whenever you want rapid navigation consistency.
Demo: Apply Freeze / Unfreeze Panes to all worksheets at once by one click with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in