 |
|
|
|
|
Download the associated Workbook for this lesson
There are times when using Excel that we do not want other people to make changes to our Workbooks, Formulas, Worksheets or Cells. We can achieve this in many ways, ranging from hiding data to preventing the Workbook from opening at all without a password.
All passwords in Excel are case sensitive.
All passwords in Excel can contain numbers, letters, symbols and spaces.
Passwords in Excel can be from a little as 1, to as many as 255 characters
There is one very important point that we should make very clear from the start and that is that Excel was not designed for high security!. Our advice is that if you need to be totally certain that nobody can access your data then Excel is not the correct tool. Excel is primarily a Spreadsheet application and as such offers security that will prevent most users from being able to access what you have protected. As far a security goes in today's world it is most probably considered low level. There are hundreds of web sites that offer password crackers for Excel Workbooks and Worksheets that will be able to gain access to loosely protected Workbooks and Worksheets.
Now, having said the above there are things we can do to make a password very hard, to impossible for a password cracker to crack. To do this we need to use a long password made up from letters (upper and lower case), numbers and symbols. When we protect a Workbook we can use up to 15 characters and when we protect a Worksheet we can supply up to 255 characters. The basics of this is, the more characters we use the harder it is to crack. The more combinations of characters we use also adds much complexity to our password.
One way we can look at this is from an old story you may have heard. Let's say we have a chessboard and we place a one cent coin on the first square and then proceed to move the one cent coin to each square on the chessboard (64 squares in total). Each time we move it though, we double the amount, so the order would be 1, 2, 4, 8, 16, 32, 64, 128 etc. In other words after 8 moves the one cent would now be 128 cents ($1.28). This doesn't sound like much but after another 8 moves we have 32768 ($327.68). By the time we have made 64 moves the one cent is worth 9223372036854780000 (LOTS!).
It is a similar principle with our passwords. By using a long password made up with a combination of numbers, upper and lower case letters and symbols we can increase the possible number of passwords one hell of a lot! Most password crackers use what is know as the brute force method. This means they run through all possible combinations until they stumble on the correct one. This can take days or months for a six character combination and may never happen for anything above this. Many password crackers will state that they do not hold much hope for a long password, so if it is important that another user cannot access your Workbook, use a long combination. One more thing, write it down and put it somewhere safe because if you forget it, you will not be able to get in either!
Let's look at the different types of protection available to us.
Excel offers us three levels of protection from within the Workbook, these are:
Protect Sheet
Protect Workbook
Protect and Share Workbook
The other method of Protection is protecting the Workbook from within the Save as dialog box.
By default all cells on a Worksheet are locked, but this has no effect at all unless we apply sheet protection. To lock and/or unlock cells we select the cells and go to Format>Cells-Protection. If all the cells are locked (default) then the Locked checkbox will be checked. If the cells are unlocked this checkbox will be unchecked. If the selected cells are both locked and unlocked this checkbox will appear checked, but greyed out. So this means that if we apply sheet protection to a Worksheet that has not had the cells default (Locked) settings changed all cells will be locked and protected. This means we cannot make changes at to any cells on this Worksheet without first unprotecting it.
Quite often we only want a few chosen cells protected from user input. To do this, it is usually easiest to select all cells via the Select All button (the grey rectangle in the upper-left corner of a Worksheet where the row and column headings meet) or Ctrl+A and unlocking all cells and then re-locking the cells you want protected. If you unlock all cells and apply sheet protection, the user will basically only be stopped from changing any formatting and other basic functions. They will still be able to copy and paste (except Cell Comments) and type directly into the cells.
In a lot of cases it will only be the cells that contain formulas you wish to protect. When this is the case you can do this with relative ease like this:
Push Ctrl+A and then go to Format>Cells-Protection and uncheck the Locked box.
Click OK.
Now push F5 and click Special check the Formulas option and click OK.
Now with all formula cells selected go back to Format>Cells-Protection and check the Locked box. You can also select the Hidden box, this will mean that formulas in locked cells will not be visible if the user selects the cell and looks in the Formula bar. But as with locking cells this has no effect unless the sheet is protected.
Click OK.
Go to Tools>Protection>Protect sheet… (Note here that you have two options checked under Allow all users of this worksheet to..... Leave them as is).
Apply a password and click OK.
You will be asked to re-confirm your password.
Click OK, then OK again.
Now all formula cells are protected, while non-formula cells are not. The password you can apply can consist of up to 255 characters.
This level of protection affects only the Workbook Structure and/or the current Window. By default the Structure is checked, while the Window is not.
By applying protection with the Structure checked we are preventing sheets (Worksheets and Chart) from being deleted, inserted, hidden, unhidden, moved and re-named.
By applying protection with the Windows checked we are preventing the Workbooks window from being hidden, unhidden, moved or closed.
As with sheet protection the password (optional) can be up to 255 characters in length.
This method of protection applies only to a shared Workbook, so before we look at it we need to understand what a Shared Workbook is. We need to take a major detour here and look at Shared Workbooks, Track Changes, Accept or Reject changes and Merge Workbooks.