Experienced in both Excel 2010 and Excel 2013.

So I've run in to this behaviour before but decided to open a quick discussion about it.

So take the following scenario, I've created a spreadsheet where a non-technical person will be performing some simple data entry.

I've configured any cells which perform some sort of calculation so they are Locked (in the example below these cells are the shaded cells), and then protected the sheet allowing only the default actions (essentially data entry only on unprotected cells, no formatting changes, etc).

This works as expected, users can enter data in the unlocked cells and cannot change any formula / data in the locked cells. The problem comes when a user has entered some figures in the wrong cells and cuts and pastes the data elsewhere in the sheet. Excel changes the references in the formula in the locked cells, subsequently breaking any calculations and the user has no idea what's going on!

It's also worth noting that it also moves the formatting as well, despite the sheet protection's intention to prohibit formatting changes.

This can be demonstrated in the video:

<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/h-92adimlWM" width="560"></iframe>

https://www.youtube.com/embed/h-92adimlWM

This seems to undermine the whole point of being able to protect worksheets! This is the behaviour I would expect from an unprotected sheet, but not when I've locked down the spreadsheet to prevent exactly these kinds of alterations.

I'll also note that copying and pasting data does not (for obvious reasons) update any formula references but will make formatting alterations to the sheet.

Has anybody else had experience with this? Am I just expecting too much of Excel ?!

Thanks,

Chris

• Edited by chrislongridge 20 hours 53 minutes ago Added Excel versions.