One of my users at work sent me an Excel spreadsheet today with a bit of a problem. This spreadsheet has one tab at the front with prices in it that are used by 12 tabs after it (one per month for a year). There are other tabs as well, all linked together with formulas doing calculations based on values across the sheets. The problem? Some of the constants on the first tab needed to be changed, but they were in "protected" cells. The cells required a password in order to change the values.
Of course, the spreadsheet is several years old, and anyone who knew the password is long gone from the company. So the user sent it to me to see if I could find a way around it.
I found quite a few programs on the web that claimed to be able to recover the password or just unprotect the cells, but they all cost money. Since my authorized spending limit at work is $0, that left me out in the cold.
Then I remembered an old trick I stumbled on in the past with an older version of Excel. Someone had given me a spreadsheet with a similar problem. Back then, I had a copy of Lotus 1-2-3 (so yes, this was quite some time ago!). Opening the spreadsheet in 1-2-3 stripped out the cell protection, because the version of the program I had didn't support it. I could then save the file off again as an Excel spreadsheet and get around the protected cells.
Well, that trick wasn't going to help me today – or was it? I decided to try opening the spreadsheet in Numbers on my Mac. When I did, I got several warnings that protected cells were not supported and so the protection had been removed. Perfect! All the cross-linked formulas still worked fine. All I had to do was to export the sheet back out as Excel and mail it back to the user.
Now, this doesn't work on a password-protected spreadsheet – where you need a password just to open it. This works on a spreadsheet that has protected cells. So it may not be something you use often, if ever, but it's a good trick to know if you need it!