Freeze Panes Disappear in Excel

You spend time setting up your worksheets exactly the way you want them – the headings are frozen at the top of the screen, gridlines are turned off, and a few other customizations are made. Beautiful!

When you open the file the next day, you appreciate all the effort that you put into setting things up. It’s much easier to work with the file this way.

A couple of days later, you cheerfully open your file again, and…what happened? All those special settings are gone. Nobody else has touched the file (or so they say), so how could all those settings have disappeared?

This occurred in John’s files occasionally, and he asked if I could figure out what was going wrong. Thanks John, for inspiring this blog post and video.

Blame the Windows

If this has happened to your files, you can probably stop grilling your co-workers, to find out who messed with your workbook. The most likely culprit is you – if you opened a second window in the workbook.

FormulaView03

For example, you might open a second window to see the cell formulas in one window, and the formula results in another window. Or you can look at two different sheet in the same workbook, side by side.

What you might not notice though, is that the second window doesn’t retain the freeze panes, gridlines and other settings that you so carefully applied. It uses the default window settings.

For example, in the screen shot below, the original settings are in the window at the right – FreezePanes.xlsx:1. The new window is at the left, and its window settings are different.

  1. Freeze Panes line missing in new window
  2. Gridlines visible in new window

windowsettings01 

Then, when you’re finished with the extra window, you close one of them. And, if you close the original window, instead of the second window, you’ll lose your window settings, and be left with the defaults. What a pain! Or should that be "what a pane!"

Close Windows Carefully

If you have opened one or more new windows in a workbook, be careful when you’re closing them. You’ll see a window number, along with the file name, in each window’s title bar.

Leave the original window (FreezePanes.xlsx:1) open, and close the other windows (FreezePanes.xlsx:2). That should prevent the accidental loss of your freeze panes, and other settings.

Watch the Window Settings Video

To see the steps for setting and preserving your Excel window settings, please watch this short video tutorial.

_____________

You may also like...

8 Responses

    • David says:

      Debra, does anyone have some code they could post please to set the freeze panes an all worksheets in a new window the same as on window 1?

  1. Yard says:

    Also remember that using File > Close will close the file and preserve all the window settings.

  2. Eric says:

    I use the Worksheet_Activate event to have some code to set the sheet the way I want it, and the Worksheet_Deactivate event to clear that code so that it doesn’t interfere with other worksheets. Use the ActiveWindow.Split, .FreezePanes, .SplitColumn, .SplitRow properties.

    Much like your earlier posts on setting formats the way you like it, I find this to be the easiest way to ensure that an individual sheet (let alone a workbook) always looks the way you want it to look.

  3. Zoran says:

    If one have more than just two windows (and different sizes, too), closing a workbook could be quite tedious task.

    However, if already have macros involved, most intuitive to me was to draw red ‘X’ shape and to assign this oneliner:

    Sub myClose_Click()

    ActiveWorkbook.Close
    End Sub

  4. Ken says:

    Always great tips here. This is a gem.

  5. savvas says:

    Thanks a lot !!!
    You describe exactly the problem I faced !!!

  6. R Shanmugavalli says:

    Thanks a lot!!!!!

Leave a Reply to David Cancel reply

Your email address will not be published. Required fields are marked *