Yesterday I encountered a new situation in Excel â€“ the â€œFreeze Panesâ€ command was disabled. This command enables you to lock certain rows or columns on-screen so that as you scroll down or to the right, the frozen rows and/or columns remain on-screen.
I wouldn't normally think much of this anomaly, but I had a situation where I had a worksheet with misplaced frozen panes that prevented me from scrolling the worksheet at all. In this article I'll describe the known situations that can cause Freeze Panes to be disabled and what to do when this occurs. If you're new to this feature, I've previously described how to implement it, as well as how to create a keyboard shortcut.
Odd behaviors in Excel aside, there are two common situations that can prevent you from being able to access the Freeze Panes command in Excel. Let's first look at the Page Layout view command, which many users rely on as their default view in Excel:
- Choose Excel's View menu.
- Click on Page Layout.
As shown in Figure 1, you can now see how the spreadsheet would appear when printed on paper. Notice how the Freeze Panes command is now disabled. Further, choosing the Page Layout command will undo any frozen panes that you have in place. To enable the Freeze Panes command again, you must choose either the Normal or Page Break Preview commands. You'll have to manually restore any frozen panes that you lost when you chose Page Layout view.
Figure 1: Excel's Page Layout command disables the Freeze Panes command and unfreezes rows/columns, as well.
I rarely use Page Layout view, so that wasn't my culprit yesterday. I simply had a large workbook that had started behaving erratically. Switching to Page Layout view and then back to Normal again eliminated the wayward frozen panes for me, although in my situation the Freeze Panes command remained disabled. It was the end of a long day, and I was using a client's computer, but most likely a reboot would have resolved the issue.
Excel's Freeze Panes command also becomes disabled when the workbook is protected in Excel 2010 and earlier, as illustrated in Figure 2. Due to changes with how windows are containerized in Excel 2013 and later, you no longer protect Windows in those versions when protecting a workbook. As shown in Figure 3, the Windows command is permanently disabled in Excel 2013 and later.
In 2010 and earlier:
- Choose Review.
- Click Protect Workbook.
- Click Windows.
- Click OK.
- Choose View.
Notice that Freeze Panes is disabled. If we had accepted the default choice of Structure only, which prevents users from moving or hiding/unhiding worksheets, then Freeze Panes would have remained available to us.
Figure 2: Enabling the Windows option for Workbook Protection disables the Freeze Panes command.
Figure 3: The Windows option is permanently grayed out in Excel 2013 and 2016 due to changes in how windows are managed.
To enable Freeze Panes again, you must unprotect the workbook:
- Choose Review.
- As shown in Figure 4, a colored background behind Protect Workbook indicates that protection is active. Click the command.
- Enter the password if prompted, and click OK.
Figure 4: A colored background appears behind Protect Workbook when protection is enabled.
Unlike Page Layout, the Protect Workbook does not undo frozen worksheet panes. It simply prevents users from unfreezing or freezing worksheet panes as long as the workbook is protected.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.