Freeze Panes Keeps Moving

[Stacey Thomas]

I have a large workbook that we use to track tax return data for various entities. There are a ton of tabs, formulas referencing tabs in this workbook, as well as formulas referencing external workbooks. We use the Freeze Pane option on many of our tabs in order to see the data more efficiently.

It seems that our freeze panes are set and then magically shift to be one row higher than we had originally set. I don't normally notice it right away - I don't think it changes until the file is saved, closed, and re-opened. Why does this happen? Does it have anything to do with the size of the file? We do also use the Filter option on these tabs, generally right below the frozen panes. Could that have something to do with it?

I'm just looking for a reason to see if we need to modify something or if it's just a known glitch in large files that have a lot going on. Any help is appreciated!!

 

[Luke M]

The filter is the culprit.

Let's say the header row is row 1, and you freeze panes right below. Then you filter some rows. Sometimes (and I haven't got it figured 100%), if you then save, close, and re-open, XL gets confused, and thinks the rows that were hidden when it opens, should be ABOVE the freeze, and so when you filter on something else (or clear the filter) it gets all screwy.

Recommendations:

• If you use an XL table, then when you scroll down, the column headers of A, B, C, etc. will automatically be replaced by your table headers, so you might not need to freeze panes.
• Alternatively, unfilter everything, setup the freeze panes, and then save. Try to make sure you always unfilter before closing workbook.

 

[Stacey Thomas]

Thanks for the recommendations! I don't think the data we have (which I can't post since it is company financial data) is best-suited for tables, so I will try your second bullet point above.