Worksheet protection

An awful lot of people advise us to protect the worksheets in our workbooks.

I take the opposite approach, I think worksheet protection should be avoided in almost all cases, and here is why:

1. it breaks all the auditing features

2. it prevents people checking your work

3. some users take it as an insult

4. it stops data validation circle invalids from working

5. it lulls you into a false sense of security

6. it is trivial to bypass, often just copying the cells to a new sheet and swapping sheets does it – well within the capabilities of many users. If not, VBA routines to remove it (and provide the password so you can re-protect it to hide your tracks) are freely available via google. Worksheet protection is not a security feature, its a usability thing, and thats the MS view, not just me.

I think you should design spreadsheets so it is hard for people to get it wrong.  If you really do need to protect your work, or your customers from themselves, then you should use something other than Excel, like Access forms, or VB6, or .net.

If you are going to protect a worksheet, you should probably protect them all and protect the structure, to make it fractionally harder for people to bypass.

What do you think, are you a protection fan or not? and why?

cheers

Simon

Advertisements

10 Responses to “Worksheet protection”

  1. Don Price Says:

    Hi Simon,

    You raise an interesting point. From my point of view, when I audit a spreadsheet, protection is one of the things I point out to the user as a feature to be used. My reasons are:

    1. Most of the users we meet are self-taught to varying standards and think they know what they are doing.
    2. Many of them physically input using the mouse to point and keyboard to type – this can (and often does) lead to poor aiming.
    3. If protection is used (with or without password), the user is forced to input via tab (not strictly true, but they are not told of the ways around this).
    4. Many users are using a spreadsheet that will be the basis of next month’s, next year’s or whatever – any changes will affect this template-like use.
    5. Over-writing of formulae is the most common cause of bottom line error that we come across.

    One of the firms I have seen has made it a disciplinary matter to remove protection from their spreadsheets.

    However, saying all that…. I entirely agree that a properly designed and written spreadsheet should negate the need for protection. In fact, a protected spreadsheet that is poorly designed could be said to be a greater risk than a poorly designed unprotected one.

    But, we still don’t agree on what is a proper design., do we?

  2. Simon Murphy Says:

    Hi Don
    Thanks for calling by, your points are all very valid.
    In terms of agreeement, we can’t seem to even agree who should drive things forward, never mind what to drive!
    cheers
    simon

  3. Ken Puls Says:

    Hi Simon,

    As you said, it only takes a quick Google search to turn up methods to remove protection via VBA for both sheet level and structure level passwords. In fact, I’ve even seen the steps on the internet for stripping VBA Project protection. It’s well known that Excel is not a secure development platform, and I take every opportunity to point that out when someone asks about protection.

    Having said that, I use protection in my work all the time. My view on protection is that it is a tool to prevent against accidental damage, rather than intentional. When a spreadsheet is “finished” for production, protection is applied, with no password, for the following reasons:
    -Ease of tabbing between data entry cells
    -Protection of accidental deletion or replacing of formulas

    I’m happy to let my users know that the sheets are protected with no password. I’m quite up front with why I use it, and also that I am not trying to stop them from intentionally altering the sheets. They also know that if they do remove protection, and mess up the worksheets in any way, that they will have to explain themselves, so they better have a good reason. So far, I’ve never had an issue. (knock on wood!)

  4. Marcus Says:

    Hi Guys,

    Similar to some of you, I really only use protection – without a password – to prevent inadvertent errors rather than malice, and this is only rarely. As much as is possible I use UserForms for data entry and typically store values in a relational database – Excel then simply becomes a calculation engine and reporting medium but not a data store.

    Another grievance is the meaningless msg which displayed when trying to edit a protected range. Regardless of colour coding on the worksheet, unsophisticated users typically ended up confused.

    Cheers – Marcus

  5. Simon Says:

    Ken I have a VBA add-in to remove VBA protection, getting the VBA IDE menus working right was harder than the code to strip the password.
    I have been stung on the worksheet protection thing (hence the ‘lulls… reaon)
    On the odd occaison I use protection I use it similar to you.
    Marcus, yes I am trying to make my Excel stuff a thinner and thinner layer too. And I agree the error messages could be more helpful.
    Has anyone ever used the DRM stuff?
    cheers
    Simon

  6. Dennis Wallentin Says:

    If I need to protect intellectual property I use COM Add-ins. Other then that I nowadays never protect any client’s VBA-solution. If the solutions stop working and / or have been manipulated so they crash then the client need to pay an agreed penalty fee (which is expensive).

    Kind regards,
    Dennis

  7. Bob Phillips Says:

    I find protection an invaluable tool to provide a good ss navigation. But like Ken says, I don’t bother with a password. If they want to break it they will, but there are other reasosn to use it.

  8. Fudgymom Says:

    I teach and use Excel and Word all the time to create templates for students to fill in when I want to grade content, not format. I lock the documents and worksheets but don’t put a password on them for the same reasons as others above have said: it’s to prevent accidental messing up by users who simply don’t know more than point’n’click and pick at the keyboard. Those who are more savvy will unlock and play, but they’re going to be more adept at using the software in the first place and so not very likely to change the important items. I do tell them, by the way, that they’re allowed to unlock. (If they say “huh?” I don’t need to worry about that format changing!)

  9. Harold Binley Says:

    Simon,
    I’m in agreement with Ken, and I also think you are either a whizz at VBA, etc. or live in an unreal world where you don’t have colleagues who rename/insert rows/cells/columns despite being asked/told/instructed not to do this as it slows down consolidations, comparisons, etc..

    Password protection doesn’t prevent people checking, (unless you go to white text or hidden cells). People do take it as an insult – but often they are the very same people who overwrite formulae, move cells around and insert rows to suit them. It can be bypassed, but mainly by those with such skills that they can probably bypass anything I produce, (only basic Excel 4 macros and VBA stuff).

    I want consistency in what comes back – and often protection is the price.

  10. Simon Says:

    Harold
    fair enough
    I do have similar users to many other folks, including ones that have bypassed protection by copying all the cells. Hence my comment of false sense of security.

    Password protection breaks pretty much all the auditing toolbar, which is what I meant by prevent checking. You can’t display arrows to precedents or dependents.

    We’ll have to agree to disagree on how much consistency you get for your protection cost.

    Delighted to read you are still using Excel 4 macros, a very underrated and underused feature.

Leave a Reply

Please log in using one of these methods to post your comment:

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


%d bloggers like this: