Oct 21 2008

Protect Cell Formats

I received a comment from a reader the other day asking this question: How can I protect the formatting of a sheet without preventing changes to the cell contents? In other words, she wanted to the sheet’s users to be able to be able to type in numbers, but not mess up her border, fonts and background colors. This Excel tip can be done in just a few easy steps. Let’s take a look at how it can be done.

Format the Sheet
The first thing you want to do is go ahead and format the sheet they way you want it to look.  Add borders, cell shading, bold, italics, anything.  Once you finish this walkthrough, you won’t have to worry about setting it again.
[ad#in-post-ad]

Unlock the Cells
Excel provides an easy way to unlock your cells for editing. 

  1. Select all the cells you want users to be able to edit. 
  2. Right click and select Format Cells from the right-click menu. 
  3. Click on the last tab, “Protection” and uncheck the “Locked” checkbox (it is checked by default.

You cells are now unlocked and you are ready to protect your sheet.

Protect the Sheet
Protecting the sheet can be done in one easy step.  From the main menu, select Tools–>Protection–>Protect Sheet.  If you want the default settings without a password, just hit OK and your formatting is now protected while still being able to change the cells contents. 

You’ll notice however there are many other options available for you to choose.  Check the options you want the user to be able to do (You’ll notice “Format Cells” is unchecked by default).

  • Select locked cells (Default: Checked)
  • Select unlocked Cells (Default: Checked)
  • Format Cells (Default: Unchecked)
  • Format Columns (Default: Unchecked)
  • Format Rows (Default: Unchecked)
  • Insert Columns (Default: Unchecked)
  • Insert Rows (Default: Unchecked)
  • Insert Hyperlinks (Default: Unchecked)
  • Delete Columns (Default: Unchecked)
  • Delete Rows (Default: Unchecked)
  • Sort (Default: Unchecked)
  • Use AutoFilter (Default: Unchecked)
  • Use PivotTable report (Default: Unchecked)
  • Edit Objects (Default: Unchecked)
  • Edit Scenarios (Default: Unchecked)

The only other decision left to make is whether you want password protection on the sheet as well.  If you don’t put a password on the sheet protection, any user can turn it off the exact way you turned it on.

** Excel Hints provides Excel Tips and Excel Help for All Levels.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

13 Comments on this post

Trackbacks

  1. sam toums said:

    Good morning,

    this tip works fine when the user has to type in data.
    As soon as the user copies/pastes data, the format cell is lost and the cell even becomes locked.

    November 24th, 2008 at 12:07 pm
  2. john said:

    Hi Sam,

    I’m going to have to research that a little more… it appears you are correct that it doesn’t work correctly with a copy/paste command. Although when I tested it, it remained locked and just the formatting was changed. Hmmm…

    November 24th, 2008 at 1:04 pm
  3. Naz said:

    Hello John, how is your research going on. I’ve been looking for this for the last 2 hours in the internet and it seems that we need a macro to fulfill our goal. What do you think?

    November 25th, 2008 at 7:07 pm
  4. john said:

    I’ve come to the same conclusion so far – a macro that can basically replace the paste command with a paste values command so you don’t lose the formatting. Seems like a oversight on their part, although there may be some instances where you might want that to happen.

    November 25th, 2008 at 10:21 pm
  5. Naz said:

    Did you able to design any macro to do this?? Thanks

    December 9th, 2008 at 12:35 pm
  6. Kinga said:

    You can use the “paste special” command to not change the formatting, but is time consuming and then you have to train other staff how to useit.

    October 8th, 2009 at 4:39 pm
  7. Naz said:

    True. That’s why i was looking for a simple method. And even after proper training, if somebody messes it up, i have to reformat the cells from scrach.

    October 29th, 2009 at 2:18 am
  8. lray said:

    this problem can be solved using the following macro code, it will paste values only and maintain existing formatting:
    Sub Macro3()

    ‘ Macro3 Macro

    ‘ Keyboard Shortcut: [insert hot key here and remove brackets]

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    End Sub

    March 12th, 2012 at 9:16 am
  9. BeResponsible said:

    Antonio Paarpp – What does your personal opinion about any one person’s life choice have to do with Excel formatting. Please use intelligence when posting to any comment page. Your immature comments about a group of people is not necessary in this comment block. No one’s day is better or worse for knowing that information. If everyone thinks something is so wrong and lame maybe we should say, “That’s so Antonio Paarpp!” or “Don’t be so Antonio Paarpp!”

    It’s low-thinking individuals such as yourself that makes the rest of the intelligent world wonder why your human race has not evolved to mesh with the rest of society that does not care what two consenting adults do alone in a bedroom together! As long as no human or animal being abused or killed, really it is not your business what people do!

    April 18th, 2012 at 4:31 pm
  10. Joseph said:

    I protected my sheet in Excel 2010. I allowed “all users of this worksheet to” Select Unlocked Cells, Insert Rows and Delete Rows. I entered and confirmed my password. Since right-clicking on a row number to insert or delete a row is deactivated, I (as will be my users) am able to insert rows from the Home Tab with the Insert drop-down function but I (we) cannot delete rows with the adjacent Delete drop-down function . Thoughts? Thanks!

    June 5th, 2012 at 10:38 am
  11. Bill said:

    I have been looking for a fix to the copy and paste function on a protected worksheet for a long time and you can not stop them from changing your format unless they paste values. many users do not know the function that well and it is a training issue. it should be easier than writing a macro which I have no experience with…

    This is a common problem and there should be a fix

    October 31st, 2012 at 2:02 pm
  12. Ginzu said:

    Has the copy problem been resolved in any way?

    January 15th, 2013 at 11:40 am
  13. Latesuggestion said:

    Sorry to bump a super old post, but I did find one work around for this problem. If you do a conditional formatting rule for the cells you want to keep formatted through copy/paste, it will work. I have it set to “format only cells with no errors” with a specific color and border. Any of the format options available to you in the format cells options would work (number, font, border, fill). So no matter how things are copy/pasted into the open cell, the conditional formatting rules will take effect.

    June 11th, 2014 at 11:46 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories