Monday Morning Mulling: July Challenge

On the final Friday of each month, we set an Excel for you to puzzle over for the weekend. On the Monday, we publish one suggested solution. No-one is stating this is the best approach, it’s just the one we selected. If you don’t like it, lump it – or contact us with your preferred solution.

Final Friday Fix: July Challenge Recap

On Friday, I waxed lyrical about freezing and unfreezing panes in Excel 2007 and later. Then, the challenge was simple – unfreeze the following worksheet from the attached Excel workbook:

If you had attempted this challenge in Excel 2013 or later, you would have realised this was not quite as simple as it sounded. ‘Freeze Panes’ was greyed out and no manner of protecting / unprotecting worksheets and workbooks would resolve this conundrum:

In Excel 2007 and Excel 2010, you may have realised you needed to unprotect the Excel workbook. In my example, I was kind enough not to have this password protected. However, in Excel 2013 and later, the workbook appears to be unprotected.

This issue has arisen from an existing bug between versions of Excel. This file was created in Excel 2003: the panes were frozen in the usual way – but then the workbook was protected, both for Structure and Windows. When this file is then opened in Excel 2013 or later, for some reason the workbook is not recognised as protected. Therefore, you have two possible solutions:

1. Open the file in Excel 2010 or earlier and unprotect the workbook (ALT + T + P + W); or
2. For Excel 2013 and later, create a new worksheet in the workbook. Highlight the entire 'Please Remove Frozen Panes' worksheet and then cut it (CTRL + X). Paste this (CTRL + V) into the new worksheet. Next, delete the original (now blank) worksheet and rename the new worksheet with the old one's name, if required.

Clearly, Option 1 is simpler and should be adopted wherever possible. The issue here is to recognise that workbook protection has caused this problem.

This example highlights one of the many perils of migrating Excel workbooks from Excel 2003 and earlier to later versions of Excel. If you encounter similar real-life issues with business-critical Excel models, feel free to drop us a line at contact@sumproduct.com where will be more than happy to assist you.

The Final Friday Fix will return on Friday 26 August with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every other business workday.