Locking Cells

Posted by Brian Evans on November 12, 2014

If you have Excel workbooks that are used often and updated frequently, it is a good idea to lock the cells that contain formulas or any data that you don’t want users to change. Getting into the habit of locking the cells with formulas can prevent users (including yourself) from inadvertently changing or erasing formulas, and it also helps direct users to where data needs to be entered.

Locking cells is simple, in fact, Excel is set as a default to lock all cells when you protect a worksheet. If you want users to be able to edit a cell (or cells) when the workbook is protected, simply highlight the cell(s) you want to unlock, right-click, and click on “Format Cells”. In the “Format Cells” window that pops up, click on the “Protection” Tab and uncheck the “Locked” Box. Once you have unlocked all of the cells that users can change, you will need to protect the worksheet.

To protect a worksheet, click on the review tab on the Ribbon and click “Protect Sheet”. Excel will then prompt you for a password, and ask you which actions you want users to be able to perform. If you choose to enter a password, and at a later time you need to update a locked cell, you will need to enter the password to unprotect the worksheet. However, you are not required to enter a password. if you leave the password blank when you protect the sheet. If you are just protecting the workbook to avoid inadvertently changing or erasing a formula, you can save yourself a couple of clicks when unprotecting a worksheet by leaving the password blank.

Setting up your worksheets to work when they are protected takes a little time during the setup, however it can save you a lot of time and energy in the long run by preventing user error.