Take the 2-minute tour ×

I am trying to "protect" a worksheet, i want to set certain cells to have certain fill functions and borders but still allow users to enter information HOWEVER(!) also want to allow the copy and paste functionto speed up information entry rather than having to manually type

share|improve this question

3 Answers 3

You can protect certain cells from all changes, but not just formatting. However, most versions of Excel support "Paste Special" where you can select to only paste values or functions (not formatting). In Excel 2007 this was changed to "Paste As" with a drop-down menu in the ribbon.

There are many combinations of what it will and won't paste.

share|improve this answer
  1. Do all of the formatting you need to do first.
  2. Highlight the cells you want to protect the format in, right-click and select "Format Cells", select the "Protection" tab and uncheck "Locked"
  3. Go to the Review ribbon, select "Protect Sheet" and in the pop-up, make sure that "Format" is unchecked.

That should do it unless I've misunderstood your question

share|improve this answer

You cannot enforce this through the standard settings. You would have to add some code to check for changes in the worksheet and set the formats back.

Alternatively, you could override the PASTE function in Excel (using VBA) so that it only ever pasted values (which is the option you want that doesn't also paste the format).

share|improve this answer

Your Answer


