Protecting cell formatting but allowing edits to values

New Contributor

I am building an Excel tool for use by many people. I would like to protect the cell formatting while allowing the users to change the values of these same cells.  Is this possible?

3 Replies

@MarkRohrbach 

Step 1:

Select all the cells whose values the users should be able to edit.

Press Ctrl+1 to activate the Format Cells dialog.

  • Activate the Protection tab.
  • Clear the Locked check box.
  • Click OK.

Step 2:

  • Activate the Review tab of the ribbon.
  • Click 'Protect Sheet'.
  • If you want to specify a password needed to unprotect the sheet, enter it. Do not forget it!
  • Click OK. If you specified a password, you will be asked to enter it again as confirmation.

Yes thanks - this allows me to lock parts of a worksheet and allow editing to other parts, but I don't think this allows me to protect the formatting of the cells I want to allow the users to edit.@Hans Vogelaar.

 

For example in the area where I want to allow the user to edit the cell (say change a 5 to a 6), I do not want the user to override my conditional formatting or data validation.

 

@MarkRohrbach 

Users won't be able to modify conditional formatting and data validation directly, but they can overwrite it by copying/pasting from elsewhere.

It is difficult to prevent this; it's best to educate users not to use the standard Paste (or Ctrl+V) but to Paste Special as Values only.