Category Archives: Protection
Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.
It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection, or has questions about just how secure a password-protected spreadsheet really is. When people know the facts without scare tactics or hyperbole, they can make the best decisions for themselves when armed with objective, unbiased information.
As protection platforms go, Microsoft’s products have inherent weaknesses. In its defense, Microsoft has never claimed to have reliable spreadsheet protection. In Office applications, a password is like the lock on your home’s front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.
Try this: open a new workbook, go to Sheet1 and protect it with the password “test” (without the quotes, lower case just as you see it here). Now unprotect Sheet1 but instead of using the password “test”, use the password “zzyw”.
Take comfort that Microsoft is like any other company, in that virtually any application is hackable. Here’s some background on Excel spreadsheet password protection:
When someone password protects a sheet in Excel, they generate a 16-bit 2-byte hash, a technical term for a number generated from a string of text by a function called the MD5 Message Digest Algorithm. An MD5 hash has fewer numeric characters than the actual password text, making it unlikely but not impossible to be replicated. Note that “replicated” is not the same as “duplicated”.
When unprotecting a protected sheet, the password value is compared to the MD5 hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Since it is a case-sensitive scheme, there are over 90 acceptable characters, which translate into the multiple trillions of password text possibilities. Since the combination of possible passwords is much greater than the combination of possible MD5 hashes, some passwords can share the same MD5 hash value.
The MD5 hash is a standard mixing algorithm, executed as follows:
- Take the ASCII values of all characters.
• Shift left the first character 1 bit.
• Shift left the second 2 bits.
• Continue for quantity of characters up to 15 bits, with the higher bits rotated.
• XOR those values.
• XOR the count of characters.
• XOR the constant 0xCE4B.
As you may know, XOR is a logical term associated with a mathematical compound statement, an acronym for “exclusive or”. In this case, statement “A” is the password value you type in. Statement “B” is the generated MD5 hash. The XOR operation returns TRUE when only one of its combinations is TRUE. This translates to more than one password value possible in the context of a truth table:
A B XOR Result
T T FALSE
T F TRUE
F T TRUE
F F FALSE
By the way, if you wanted to reproduce the actual password, and not just a compatible one, it’s a virtual certainty that it literally could not be accomplished during your lifetime.
There are 94 standard characters (26 of A-Z; 26 of a-z; 10 of 0-9; and 32 special such as #,%,!, and so on). That means, for every character there are 94 possibilities.
To extrapolate using the example of an eight-character password, the number of characters to test is
94 x 94 x 94 x 94 x 94 x 94 x 94 x 94
At the hefty pace of 100,000 password attempts per second, it would take 1,932 years to recover the exact password. And that’s just with 8 characters; with the 255 max it can take millions of years.
What all this boils down to is, if you don’t want to expose your Excel spreadsheets to *any* possible password circumvention, don’t share them. However, the likelihood of someone guessing a compatible hash is very slim, though there are commercially-sold password cracking programs.
One thing is sure, you are in good company: the whole world is in the same boat with this Excel protection issue. As you understand the spreadsheet password protection scheme, you can make your own informed decisions about what and what not to risk putting in your workbook, and how or with whom you share access to your workbooks.
Blog kindly provided by Tom Urtis (Excel MVP). If you are interested seeing more blogs and tips by Tom follow him on Twitter (@tomurtis) or visit his website Atlas Consulting
Having spent a lot of time creating the perfect workbook, setting it just how you need it ready to do other important work, you send it out to people around the office or business, or perhaps you place it on a shared drive for everyone to access, only to find that within days, or even hours, some bright spark doesn’t like your layout and changes it around to suit them regardless of the fact that it was set up in a particular way for a specific reason.
This is where we need PROTECTION.
To protect a workbook there are number of steps to follow:
- Unlock any cells that the user(s) need access to – this will apply to all visible sheets.
- Protect any sheets users will have access to, and hide any sheets users don’t need access to.
- Protect the workbook itself
A quick word first about the effectiveness of protecting workbooks. Basically, it’s not very secure. There is software readily available for free off the internet that will quickly hack into a workbook – I use one myself by Rob Bovey at www.appspro.com called Excel Utilities v.7 (check out my video on WordPress/YouTube – “Clearing Workbook & Worksheet Passwords” showing how well it works http://wp.me/p2EAVc-3u).
You’re probably thinking “so what’s the point of protecting my work if someone can hack into it so easily??”, but in the majority of cases, especially in the work environment, many companies don’t like or even allow the use of freebie add-ins off the internet and to be fair, most people couldn’t be bothered hacking into a workbook at work…..why create more work for yourself?
Step 1: Unlocking Cells
When you open the FORMAT CELLS dialog box you may have noticed a tab on the far end called PROTECTION.
You may have tried ticking and unticking the options and seen no difference whatsoever to your spreadsheet, but notice the comments below the tick boxes – “Locking cells or hiding formulas has no effect until you protect the worksheet”. However, this step is vital once protection has been applied to control what people have access to in the worksheet.
All cells, by default, are locked. Therefore, select ALL cells that you want people to be able to edit or fill in and untick the LOCKED option. Any cells you don’t want people to change or enter information into, leave as is – locked!
Using our timesheet example that we used to create VALIDATION rules, the cells highlighted in blue have been unlocked in preparation for PROTECTION.
Once you are satisfied that all the correct cells have been unlocked go on to step 2…
Step 2: Protect Your Worksheets
Before protecting every sheet in the workbook, stop and think about which sheets people actually need to use and/or see. For example, you may have a sheet where all the lists that are used in your DATA VALIDATION lists are kept. Do people need to see those? Probably not. So rather than set up a password etc. to protect the sheet, hide it instead. Do the same for any other sheets that people don’t really need to see or access. Protecting the workbook in step 3 will prevent people gaining access to all your hidden worksheets.
Go to the REVIEW tab and click on PROTECT SHEET.
In the following dialog box you have the option to create a password. Personally I don’t set a password until I have tested everything to make sure it works how I want it to. If you set a password while still testing, it just means you have to type in the password every time you want to make a change and reset it again afterwards. A minor pain….your choice.
You can also decide what freedoms you are going to give to your users. Do you want people to be able to add or delete rows or columns etc.? The only option you must leave ticked is SELECT UNLOCKED CELLS. If that is unticked, you can’t even select the cells you want to edit!
Test your settings. Can you get to all the cells that need to be filled in or edited? Are all the cells you want to block access to safely locked? Lock or unlock cells as appropriate and re-test.
Once fully tested and working OK, set a password, confirm the password in the next dialog box and click on OK.
Now that the worksheet(s) are protected you’d think that would be enough. A lot of options, including DATA VALIDATION have been made inactive and appear greyed out in the ribbon.
However, if you right click on any of the worksheet tabs you can still rename, delete and move worksheets.
To deny all access to this you now need to complete step 3 – protecting the workbook.
Step 3: Protect Your Workbook
Click on your REVIEW tab again, and click on PROTECT WORKBOOK.
Protect the workbook, with or without a password, and test it. Try to delete or rename sheets, try to get to validation lists etc. Make sure you check EVERY sheet. Protecting a workbook does not give any cell protection to a sheet that has not been protected, leaving users free to edit any content on that sheet.
Once fully tested, apply passwords and you are ready to unleash your workbook on the world.
Unless you have Rob Bovey’s Excel Utilities and the break passwords function (or equivalent) DON’T FORGET YOUR PASSWORDS! Making copies of the workbook or other similar tricks won’t work – it will copy all the passwords across at the same time.
As an optional extra, you can also apply protection to individual cells or ranges of cells that require a password to gain access.
Let’s take out timesheet example again. We have unlocked all the cells that people need access to, but there is a column called “Approval Status”. This is where a manager has to approve/reject the line recorded in the timesheet. If I leave this unlocked, then the person filling in the timesheet would have access to these cells and self-approve their timesheet.
By leaving this set of cells locked, the general user cannot access them, but I need the cells to be accessible by the manager. For that, I can use ALLOW USERS TO EDIT RANGES.
Select the range of locked cells and on the REVIEW tab, click on ALLOW USERS TO EDIT RANGES. Click on the NEW button on the dialog box that appears. On the next dialog box give your range a TITLE (e.g. Approvers). If you have highlighted the cells, the REFERS TO CELLS box should show the cell range. If not, simply click on the range selector button and highlight the range you want. Finally set a password, confirm it and click on OK.
Now if anyone tries to type into one of these cells they will be prompted for a password.
So with a combination of validation rules and protection your workbook should be fairly safe from interference from the majority of people.