MS Excel: A Locked Cell is Not a Cell Protected From Changes
If you’ve ever found yourself nosing around the Protection tab in the Format Cells dialog box then you’ve probably found that the Locked check-box was selected and possibly thought that your worksheet was safe from changes made by others…
Whew! We’re safe – right??
Well, not really.
Locked isn’t actually protecting the cells from change, it’s marking those cells that should be protected from user changes if you actually choose to protect the worksheet.
By default, the entire worksheet is selected as locked – but not actually protected.
If you want to protect and entire worksheet from changes once it’s ready, you need the Review tab of the Ribbon, Protect Worksheet (or Workbook) button.
Once selected, a new dialog box will open where you’ll need to select which actions you do want to allow users to make as well as a set a password for un-protecting the worksheet.
You will be asked to confirm your password so there are no typos that will result in you being locked out of your own worksheet.
That’s all fine and dandy, but that process locks the whole worksheet. What if you want to protect only certain cells in the worksheet?
If protecting only a few cells is your goal, then select the entire worksheet, use Ctrl+1 to get to the Format Cells dialog box. On the Protection tab un-check Locked and click OK.
Now you’ll have to select only those cells you actually want to protect from changes, return to the Format Cells dialog box (Ctrl + 1), Protection tab, check the Locked option and click OK.
With just those cells locked, when you go to the Review tab and actually protect the worksheet, they will be the only ones barred from changes because they were the only ones locked.
Conversely, if you have only a few cells that are to be left unprotected, select those few cells, go to the Format Cells dialog box (Ctrl + 1), Protection tab and un-check Locked. Click OK.
This time when you go to the Review tab and protect the worksheet, all cells except the ones you selected and changed the setting for will be protected.
No matter what portion of your worksheet you need to protect, it’s a two-step process.
- Lock the cells to be protected.
- Protect the worksheet via the Review tab of the Ribbon.
That’s all there is to it… once you understand the difference between a “Locked” cell and a “Protected” cell.