Protect your cells in #Excel

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.

Most actions are disabled when a worksheet is projected

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. 😉

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.