Excel – Protecting the formatting in Excel 2007

formattingmicrosoft excelmicrosoft-excel-2007

I'm creating a number of forms in Excel 2007 for other people to use. They will be accessing the digital versions, and I'm afraid that they'll eventually accidentally break the formatting I've created. The most likely way they'll accidentally break it is by using copy/paste.

Is there any way to lock the formatting while still allowing the contents of cells to be modified? By formatting I mean:

  • Cell Fill Color
  • Borders
  • Merged Cells
  • Font type/height/formatting
  • Contents of some cells (Cells that Label)
  • Cell width/height (This is the least important, since it's unlikely they'll accidentially change this.

To be clear, I'm not trying to protect from malicious users. I'm trying to protect from well-meaning users who don't entirely understand excel and excel formatting. While I can teach them some basic tips on avoiding breaking things, in the end it's going to be necessary to have at least some basic format lock in place.

Best Answer

  • A combination of 2 settings provides you this functionality:

    1. Select the cells where data entry will be performed, go in Format Cells, Protection and uncheck LOCKED.

    2. On the Ribbon, under Review, PROTECT your worksheet. Notice that some exceptions are available: Select, Format cells, Insert, Delete, etc. Just leave them all unchecked, except Select. If your users need to insert rows, also include the exceptions to "Insert rows" (and perhaps "Delete rows").

  • Related Question