In the last post, we looked at how to protect an entire workbook in Excel. When sharing workbooks with others however, you may in certain instances need to protect particular areas of your worksheets from edits. For example, you may want to lock any cells containing formulas to prevent accidental changes. Once worksheet protection is in place, the locked data in a cell or cell range cannot be changed or deleted.
In order to protect a cell or cell range from data changes, you will first need to ensure that any cells in which you want to allow changes are in an unlocked state. By default, all cells are locked and once you protect a worksheet, locked cells are no longer editable. Once the cells that you want users to be able to access are unlocked, then you can apply protection to the desired area, either the active worksheet or the entire workbook.
- Click the Home tab on the Ribbon.
- Select the cell or cell range you wish to unlock.
- Click the Format button on the Cells group.
- Click the Lock Cell command on the menu. This is a toggle command that locks or unlocks a selected cell range.
Tip: You can also lock and unlock cells from the Protection tab of the Format Cells dialog box.
After you’ve unlocked any cells that you want to allow the user to edit, you are now ready to apply protection to your worksheet. Once protection is in place, you will not be able to make any changes to locked cells without first turning off protection.
However, when applying protection to a worksheet, you can set certain options such as allowing users to format, insert, or delete cells, columns and rows, sorting data, etc. Additionally, you can set a password for the protected worksheet. This is an optional (but recommended!) setting that allows anyone who knows the password to turn off sheet protection. If no password is set, anyone can unprotect your worksheet.
- Click the Review tab on the Ribbon.
- Click the Protect Sheet button on the Changes group.
- Type a password in the Password To Unprotect Sheet text box.
- Select any options from the Allow Users Of This Worksheet To: list to specify options that you want protected from edits.
- Click OK.
- Confirm the password in the Confirm Password dialog box.
- Click OK.
Tip: Another way to protect a worksheet is by clicking the Protect Workbook button in Backstage view and choosing Protect Current Sheet from the menu. Set any desired options.