Level 2 - Lesson 31 - Protection
Category: [General,Excel] Demo Available
Protection in Excel
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.
Protection
Excel offers us levels of protection from within the Workbook, some of these are:
- Formulas: If you don’t want other users to see your formulas, you can hide them from being seen in cells or the Formula bar.
- Ranges: You can enable users to work in specific ranges within a protected sheet.
Enable worksheet protection
Step 1: Unlock any cells that needs to be editable
- Select the worksheet tab that you want to protect.
- Select the cells that others can edit. You can select multiple, non-contiguous cells by pressing Ctrl+Left-Click.
- Right-click anywhere in the sheet and select Home Tab click on the Alignment group launch icon (or use Ctrl+1, or Command+1 on the Mac):
- And then go to the Protection tab and clear Locked
Step 2: Protect the worksheet
Given below are the steps to protect your sheet.
- On the Review tab, click Protect Sheet (or right click on the tab name of the sheet and select Protect Sheet).
- In the Allow all users of this worksheet to list, select the elements you want people to be able to change.
- Optionally, enter a password in the Password to unprotect sheet box and click OK. Reenter the password in the Confirm Password dialog box and click OK.
Important:
- Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Passwords should be 8 or more characters in length. A passphrase that uses 14 or more characters is better.
- It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples