Protect Cell Formats
- 8 Comment
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.
Unlock the Cells
Excel provides an easy way to unlock your cells for editing.
- Select all the cells you want users to be able to edit.
- Right click and select Format Cells from the right-click menu.
- 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
8 Comments on this post
Trackbacks
-
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 -
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 -
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 -
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 -
Naz said:
Did you able to design any macro to do this?? Thanks
December 9th, 2008 at 12:35 pm -
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 -
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 -
ANTONIO PAARPP said:
Yeah, same gay problem. copying and pasting ruins the formatting
January 23rd, 2012 at 9:10 am
