So far, you’ve seen a few different methods to prevent errors from being generated in your spreadsheets, but those methods won’t prevent someone from making unauthorized changes to your worksheet or workbook that cause errors. Another person could cause problems if they make changes to cell formulas or delete cells. You can prevent that from happening by using worksheet protection to lock the worksheet.
In general, the way to protect a worksheet is through the Review tab. Select Protect Sheet in the Changes section. A box will appear:
The default options above will allow users to select cells, but not edit them. Click OK to test this. You’ll be able to select any cell in the worksheet. However, if you try to edit one, you’ll get an error message.
By default, Excel makes every cell a locked cell, so you can’t edit anything. The worksheet is totally locked and could just as well be a PDF file. Instead, you can selectively unprotect cells that you want to be editable.
Click the Unprotect Sheet button in the Review tab. Now you can unlock the cells in the Input table. Select those cells (C5-C9), right-click, and choose Format Cells. In the window that appears, switch to the Protection tab. Unselect the check box next to “Locked” and click OK.
Click Protect Sheet again. Now, the input cells are unlocked and can be edited. However, the cells containing formulas are locked, so other users can’t alter your calculations. This is a good way to allow others to perform calculations by changing the inputs without modifying the formulas.
In this state, the sheet could be unprotected by anyone, because there’s no password applied to the worksheet. Click Protect Sheet, and enter a password:
Excel does not have any password recovery option, so if you forget this password, you won’t ever be able to unprotect the sheet. It’s recommended to record your password somewhere.
Click OK. There’s a two-step verification to make sure you typed the password correctly. Enter the password again, and click OK.
Now, if you want to unprotect the sheet, you will be prompted for your password. There’s no link to click if you forget your password, but if you enter it correctly, the worksheet will unlock and you can modify it again.
When you protect a sheet, you can control what the user is allowed to do in this window:
If you want to prevent users from selecting cells that are locked, you can unselect Select Locked Cells. The user will still be able to edit the unlocked cells, but they won’t be able to click the locked cells (or see their formulas). You can also decide if you want to allow users to do other things such as formatting cells, columns or rows; inserting or deleting columns and rows; sorting and filtering data; etc.
Another type of protection in Excel, in addition to protecting the sheet, is protecting the workbook. Protecting the workbook isn’t quite as useful as protecting a sheet.
This allows you to prevent changes to the structure of the workbook. Users won’t be able to rename worksheets, change their order, insert/delete sheets, hide/unhide sheets, or change the tab color.
Excel gives you a high degree of control over your worksheets using the methods you’ve seen in this chapter, like data validation, form controls, and worksheet protection. These methods will allow you to prevent troublesome errors, especially if anyone else uses your worksheets.
