Excel Tip: Worksheet Protection
The purpose of worksheet protection is to prevent changes from occurring to the worksheet (not to be confused with Workbook). You can prevent users from changing cell contents and structure for example.
Worksheet protection is very useful in templates and shared workbooks. You as the workbook CREATOR may have certain formulas and cells that you do not want the workbook USER to change.
Detail
It is very simple to implement worksheet security. It can be applied with or without password protection. Depending on the sophistication of the users, and how critical the protection is will determine if you implement passwords or not. In any event, the password can be easily bypassed by a savvy user, so don’t rely heavily on Excel passwords for real security.
Exercise
Let’s say we have a worksheet and we only want the user to be able to enter data into a specific range of cells (the input area). We do not want the user to be able to add/edit/delete any data in any of the cells outside of the input area. This is easily accomplished by implementing worksheet security.
The first step is to define which cells the user will be allowed to edit. The second step is to secure the worksheet.
To define the cells that the user can edit, simply highlight the range of cells you wish to ALLOW the user to edit, and select Format -> Cells menu item. Click on the Protection tab and uncheck the “Locked” checkbox, as shown in Figure 5 below. It is important to note that the default setting for all cells in a worksheet is “Locked”. So you therefore, “unlock” the cells that you wish the user to edit. Note that even though the default setting for all cells is locked, you can edit the cells UNTIL you implement the security. That is, the cells are not actually locked until you turn on worksheet protection.
Figure 5
Once you have unlocked the cells you wish to allow the user to edit, turn worksheet security on by simply selecting the Tools -> Protection -> Protect Sheet menu item. You will be presented with the dialog box shown in Figure 6 below.
Figure 6
Enter a password to require a password when unprotecting the sheet, or leave the password blank if desired. You will now notice that only the cells that have been unlocked can be edited! If the user tries to enter text into a locked cell, they receive the error message shown in Figure 7 below.
Figure 7