Excel Protect Your Worksheet (or part of it)

When an excel worksheet is shared among multiple team members there is sometimes a need to restrict the parts of the worksheet that are open for changes and keep other parts of the worksheet protected from manual changes. It is not necessarily an information security control but more likely a productivity issue to protect against human errors of edits done to static or calculated cells.

Excel supports this scenario very well by selecting specific sheets, columns or even cells to be protected from changes. You can also decide what level of restrictions you would like to enforce, such as allowing copy/paste or not, allowing format changes or not and more. Let’s see how it is done…
The simplest way to setup protection is for the whole worksheet or to a specific sheet. For that on the main menu select “Review” and either select “Protect Sheet” or “Protect Workbook”. You will see the protection menu:

2014-01 Excel.data.protection.1

The first thing you could do on this menu is to setup a password too be used to unprotect the sheet or worksheet. The second part is related to what can and cannot be done on the locked cells. We will explore what it means the “locked cells” but for now it is enough to note that by default all the cells are locked. In this the two top options are checked and the rest of the options are unchecked. Which means that both locked and unlocked content can be selected and copy, but the locked content will not allow any changes to these cells.  And if a user tries to make a change to the content they will encounter the following message:

2014-01 Excel.data.protection.2

So this is cool but what if the same sheet includes some data that should be protected and some data that should not be protected. This is where the definition of “Locked Cells” and “Unlocked Cells” comme to play.

Let’s say that we need to protect column A but we want column B to be still open for everyone to change. To achieve that, we select column A, open the right mouse menu and select the “Format Cells…”. A new popup dialog will open and we select the right most Tab called “Protection”:

2014-01 Excel.data.protection.3

The “Locked” checkbox will be checked. Now we do the same for column B (that we want to enable for changes) and uncheck the “Locked” checkbox.

At this stage we should have column “A” with “Locked” checked and column “B” with “Locked” not checked.

Now when we the sheet is in Protected mode, column A will be locked for changes while column B will be open for changes.

See even more details and examples at the Microsoft site:

http://office.microsoft.com/en-us/excel-help/lock-cells-in-a-worksheet-HA010342979.aspx

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

1 thought on “Excel Protect Your Worksheet (or part of it)”

  1. I received a comment on the security aspect of protected excel worksheets. I should say lack of. The comment was written in “i hate” way so it will not be published “as is”. But the comment itself is correct. I’ll expend on that in a different post…

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s