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 Friday, May 29, 2015 10:35 AM Added Excel versions.

Hi George,

Thanks for your reply. The issue is not so much how the formula are updating, but whether they should at all on locked cells on a protected sheet, essentially allowing the end user to modify or break the worksheet against the will of the creator.

That said, the references attempt to update regardless of absolute referencing or otherwise. And you can still alter the formatting of the worksheet (despite it being locked, and the formatting permissions disabled) by cutting and pasting.

I've drafted a quick demo worksheet on and shared it on my OneDrive here, where you can cut the two columns from the left, to the right and watch the formula in locked cells change.

Thanks,

Chris

Edit: Should you require, the password to both the worksheet and workbook is "test"

• Edited by chrislongridge 18 hours 30 minutes ago

Hi George,

Thanks for your reply. The issue is not so much how the formula are updating, but whether they should at all on locked cells on a protected sheet, essentially allowing the end user to modify or break the worksheet against the will of the creator.

That said, the references attempt to update regardless of absolute referencing or otherwise. And you can still alter the formatting of the worksheet (despite it being locked, and the formatting permissions disabled) by cutting and pasting.

I've drafted a quick demo worksheet on and shared it on my OneDrive here, where you can cut the two columns from the left, to the right and watch the formula in locked cells change.

Thanks,

Chris

Edit: Should you require, the password to both the worksheet and workbook is "test"

• Edited by chrislongridge Tuesday, June 02, 2015 1:00 PM

Hi chrislongridge,

I couldnt download the Test Workbook on your OneDrive, please check the Test Workbook which you uploaded.

If possible, you could send this file to our email address:

ibsofc@microsoft.com I'd like to help you check it.

Please Note: The email subject you should use the URL or the Title Name of your case.

Thanks for your understanding.

Regards,

George Zhao
TechNet Community Support

I haven't looked at your file but I think I follow. It's not a bug but as expected, though not necessarily what's wanted.

There are various VBA approaches to disable the cut/copy but difficult to completely avoid. Depending on your scenario maybe something like this might mitigate

In F5 =D5*10

Name F5 as "Calc1"

Protect the sheet with F5 locked and most of the rest unlocked but particularly D5 unlocked 

In the worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const calc1 As String = "=D5*10"
    If Range("Calc1").Formula <> calc1 Then
        Me.Unprotect  ' pw
        Range("F5").Formula = calc1
        Me.Protect ' pw
    ''or maybe
'        MsgBox "Don't mess with me again, bye"
'        ThisWorkbook.Close False
    End If
End Sub

Instead of un/protecting maybe protect with UserInterfaceOnly

• Proposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 1 hour 33 minutes ago