Excel protection

After an Excel course I was running at the highly impressive Sage HQ in Newcastle, one of the delegates pointed out that it’s all very well protecting your spreadsheets, but how do you stop users cutting and pasting between unlocked data cells? The effect of this is to potentially mess up the references to the data cells affected even in the protected cells. Of course it would be possible to write VB code to prevent this, or to use form fields or some other method of data entry, but is there a simpler way?

In Excel XP and later, the protection capabilities have been substantially enhanced and allow choice as to what a user can and cannot do. In addition, there is the ability to ‘Allow users to edit ranges’. I wondered whether this might be the solution, so tried leaving all the cells in a sheet locked, but then adding the data cells to an ‘allowed range’ – in this case without setting a password. This did seem to work. When the sheet was protected the protection did not allow cutting and pasting in any locked cells (which was of course all the cells – including those in the allowed range), but because of the allowed range, with no password, data could be entered in these cells. I don’t know whether this is a well-known feature and obvious method of implementing protection since XP changed the protection options, or an unintended but useful consequence. I also haven’t yet tested the way it works thoroughly and am waiting to hear how well the solution works for the person who asked the question in the first place.

Excel protection

2 responses to “Excel protection

  1. Hi Simon

    I’ve used this approach and it does seem to work. I don’t see other users using this feature often and for the casual user at least it does not appear to be obvious.

    Given the problems with errors in spreadsheets I would prefer it if Microsoft made this feature both more obvious and user friendly It would be helpful if you could toggle the sheet to clearly identify the protected cells. These could be highlighted by shading or something similar.

    Good luck with your blog!

  2. This is fine if you want to stop ‘cut and paste’ but does not stop ‘copy and paste’.

    I have a backup report which has ‘data centre’ and ‘offsite’ components. Failures due to tapes not being loaded ‘offsite’ are exempt from SLA, where as those at data centre are inluded in SLA.

    Problem is that operators can ‘copy and paste’ the ‘exempt’ code even if the selection is not available from conditional input…..

Leave a Reply to Philip Woodgate Cancel 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 )

Connecting to %s