Think about this… you have spent hours of work building your wonderful Excel template. Needless to say there are lots of formulas that took you hours of thorough thoughts and considerations, on top of the beautiful formats and layouts. You are very happy with it and you share it to your colleagues. Just a few minutes later, your colleague came to you and said your formula did not work; many errors came out; and seems that someone had “carelessly” messed up some formats and layouts…

You opened the workbook and you found that someone

1. deleted rows and columns that they are not suppose to…
2. inserted rows and columns at wrong positions…
3. applied different color-filled and font sizes in an ugly way…
4. and many more…

Your mood travelled from heaven to hell in just a few minutes.

Is it familiar to you? If it is, you have to know how to protect yourself cells from editing.

To protect a worksheet

1. go to Review tab on the ribbon
2. select Protect Sheet
3. enter a password if needed (you will be prompted to re-confirm the password)
4. select the action(s) that you would like to release to users (by default, only the first two options are checked” (see the list of actions in the final section)
5. OK to confirm

As simple as that.

(Note: Protecting a sheet from editing is different from protecting the workbook from opening.)

Assuming we have not changed the default selections in step 4 above, users are still able to select cells (be it locked or unlocked) but fail to edit at all. Whenever they attempt to edit a locked cell (or even to apply a format to it), they will see the following message:

Yeah! No one can edit the locked celled in the protected sheet anymore, unless they know the password to un-protected the sheet (or know how to hack into the protected sheet).

Tip: When a worksheet is protected, many buttons on ribbon are grey out.

To unprotect a sheet

1. go to Review tab
2. select Unprotect Sheet
3. Enter the password then OK (note: you won’t see this step if no password was set up initially)

By default, all 17179869184 cells on a worksheet are “Locked“. That’s why user cannot edit any cells at all by default unless we “unlocked” certain cells before protecting sheet.

To unlocked cells

1. Select the cells you want to unlocked
2. Right-click the selected cells (Shortcut: Ctrl+1) to open Format Cells dialog box
3. Go to Protection tab
4. Unchecked the “Locked” checkbox (which is checked by default)

The default setting makes sense to me. Most of the time, we would like to protect the entire worksheet with only a few exceptions where we put variables. In this sense, it’s easier for us to select only certain cells to unprotect them when required.

To lock only a small number of cells

However, there are situations we want to protect only a few cells while leaving all other cells editable to all users, then we we need to unlock all cells first. To do so,

1. click the upper-left corner of a sheet to select the entire sheet (or press Ctrl+A in an empty range)
2. right-click (or Ctrl+1) to Format Cells
3. Go to Protection tab
4. Uncheck the “Locked” checkbox
5. OK

Then we select the cells we want to lock and repeat the steps to protect cells. This time, we check the “Locked” checkbox instead.

Remember: only locked cells will be protected from editing when the worksheet is protected.

What does “Hidden” mean?

At this point, you may be wondering what does the “Hidden” under “Locked” mean?

Its meaning is stated subtly in the dialog box. It hides the content from visible in the formula bar… it has no effect until you protect the worksheet.

What action can we allow users to do in protected sheet?

Indeed, all the actions are listed in the dialog box and they are quite intuitive.

1. Select locked cells
2. Select unlocked cells
3. Format cells
4. Format columns
5. Format rows
6. Insert columns
7. Insect rows
8. Insert hyperlinks
9. Delete columns
10. Delete rows
11. Sort
12. Use AutoFilter
13. Use PivotTable and PivotChart
14. Edit objects
15. Edit scenarios

Do you protect your cells? Or have you experienced any “out-of-the-box” way that users could still destroy a protect sheet? Please share it with us in the comments below. 😉