7/9/04 Protecting selective cells in Microsoft Excel

by Lee Koo (ADMIN) ModeratorCNET staff - 7/8/04 1:13 AM

(A word of caution: If you decide to password-protect those selective cells, make sure you don't forget the password, because if you do, Microsoft won't be able to bail you out. Thank you.)


Thank you Brian and all the participants in this weeks answer!
Make sure you check out all the honorable mention below which include many additional tips from our members. I encourage all of you who have more tips or questions on this topic to post below. This way we can all learn more together.

Thanks again everyone!
-Lee Koo
CNET Community



Question:

In Microsoft Excel, how can I protect selective cells in a worksheet (the ones that contain formulas) from accidentally being altered or deleted?

Submitted by Chris H. of Hua Hin, Thailand


Answer:

By default, all Excel worksheet cells have locked definitions associated to them. This function will take effect only if the worksheet has been protected, and it covers all cells in the protected worksheet or workbook.

If you want to protect only specific cells, such as those with formulas, you would first need to unlock all the cells on the worksheet, lock the cells that you want to protect, and protect the worksheet.

Steps:
1. Select all of the worksheet (hold down the Ctrl key and A) or all of the working cells in your sheet (hold down the Ctrl, then select Cells).
2. When the selection has been made, right-click the mouse and select Format Cells from the drop-down window.
3. Click the Protection tab, deselect the Locked check box, and click OK.
4. Now click the specific cells that you want to protect. You can select them all at once by holding down the Ctrl button on your keyboard while you click each cell.
5. Once all cells are selected, right-click the mouse again, go to Format Cells, click the Protection tab, and this time select the Locked check box and click OK.
6. The final stage is to protect the worksheet. Click Tools, go down the menu to Protection, click Protect Sheet, then click OK. A password is optional here. If you want to password-protect it, enter a password (you will need to reenter it to confirm), then click OK. Don't forget your password because there is no way to retrieve it.
7. Save your work!!

Now your worksheet is protected, and locked cells cannot be tampered with unless you follow step 6 to Unprotect sheet or open it with the protection password you assigned to it.


Submitted by: Brian H. of Israel


****** HONORABLE MENTIONS**********

Answer:

How to protect selected cells in Excel

I create templates with protected cells for use by a couple dozen employees with widely varying computer skills. On these sheets it is critical that Descriptive Titles as well as Formulas which calculate amortization schedules are not accidently or deliberately tampered with.

Here's a quick way to accomplish this.
1 First Select the entire worksheet by clicking on the upper left most cell in the border area of your worksheet. This "grayed" cell is blank. It contains no Row or Column identification numbers or letters.
By clicking in this cell, you select the entire worksheet with just one quick click.

If your Tool Bars already display the "Lock Cell" icon just click on that icon. If no icon is displayed on your Tool Bars, go to FORMAT / CELLS / and Select the TAB labelled "Protection". On this TAB check the "Locked" radio button. Press "OK"
All cells are now locked.

2 At this point you wish to "un-lock" only those few cells on your worksheet in which you wish your end users to "input" data. Consider leaving Titles, Descriptive words, as well as all Formulas locked and protected. It is amazing what a dozen end users can do to discover and corrupt any "un-locked" cells within a worksheet.

When you are ready to "Un-lock" a select, few cells, Select only those cells which you wish to un-protect for end user input.

You may select these cells separately by clicking on a cell with the mouse while holding down the "Control" key. Or, you may select contiguous cells by dragging your mouse. And hen hold down the "Control" key and select the remaining non-contiguous cells.
With your selection made, go to FORMAT / CELLS / and Select the TAB labelled "Protection". On this tab un check the "Locked" radio button. Press "OK"

3 Now go to TOOLS / PROTECTION / PROTECT WORKSHEET

Enter a Password.
Please choose your password carefully because several months from now, you may forget your password. MicroSoft issues a warning : If you call MicroSoft for assistance with a lost worksheet password, even MicroSoft can not bypass this type of password.
When you have entered your password, Press "OK"

You have now protected the entire worksheet.

As a test precaution, you may now try to enter numbers in the worksheet to test if the cells are protected where you wish to protect them, and remain un-protected where you wish to allow user input.

If you find a cell that got overlooked, just go to TOOLS / PROTECTION / UN PROTECT WORKSHEET. Enter your password, press "OK."
In your worksheet now select any overlooked cells and go to FORMAT / CELLS / and Select the TAB labelled "Protection". On this Protection Tab check or un-check the "Locked" radio button as needed for that cell.

When you are satisfied that the entire worksheet is tested, proofed, and running as intended, return to TOOLS / PROTECTION / PROTECT WORKSHEET. Enter a Password. Press "OK".

There is a last measure of "protection," available to you beyond the "cell and worksheet protection" that you did not ask about.
I would advise you to consider saving this file as a Template. By creating a template, many future end users will be able to use your Template to create their own saved files, with their own separate and individual names on each file. And despite the numerous uses of this Template, the end users will never be able to accidently tamper with your original Template.
To save this file as a Template, go to FILE, SAVE AS.
In the dialog window at the bottom select SAVE as File Type, " *.XLT " Name the file and click SAVE

Your End Users will be able to access this Template by simply going to File New.

Submitted by: Jim M. of Asheboro, North Carolina

**********************************************************************

Answer:

You can prevent cells in MS Excel from being amended or deleted by protecting them
The default protection level is for ALL cells to be protected. Therefore you must first decide whether you wish to protect a few or most. If you have only a few cells that need to be protected continue as below.

1. In the first case select all the cells in your worksheet by clicking on the small box at the junction of the row and column headers.
2. Select Format.Cells and in the resultant dialog box choose the "Protection" tab and clear the "Locked" check box. Click [OK].
3. Holding down the [Ctrl] key select those cells you wish to protect (or the row or column number/letter if you want to protect entire rows/columns).
4. With these cells selected, click on Format.Cells and in the resultant dialog box choose the "Protection" tab and check the "Locked" check box. Click [OK].
5. Now click on Tools.Protection and select either Protect Sheet or Protect Workbook. You may set a password but you do not have to if you are only trying to avoid accidental deletion. Your chosen cells are now protected.

In many cases a spreadsheet will have a handful of cells in which you enter data and the remainder either contain formulae or nothing and can be protected. This is slightly easier as you have only to change the protection level of cells that can be amended.

1. Holding down the [Ctrl] key select those cells, rows or columns you wish to unprotect.
2. With these cells selected, click on Format.Cells and in the resultant dialog box choose the "Protection" tab and uncheck the "Locked" check box. Click [OK].
3. Now click on Tools.Protection and select either Protect Sheet or Protect Workbook. You may set a password but you do not have to if you are only trying to avoid accidental deletion. Your chosen cells are now unprotected and all the other cells in the Sheet or Workbook are protected.


Submitted by: Chris D. of the United Kingdom

**********************************************************************

Answer:

Hi Chris

It is easy to protect cells in a worksheet.

By default, all cells are locked. However, it has no effect unless the worksheet is protected.

Here's what you have to do:

To protect all the cells in a worksheet :
1. Select all the cells. You can do that by clicking the rectangle on top of row 1 and on the left of column A
2. Right-click anywhere within the worksheet, click "Format Cells" and choose "Protection" tab
3. If the box on the left of the word "Locked" is not checked, click to check and click OK

For Excel 2000:
4. Go to "Tools" Menu, choose "Protection", choose "Protect Sheet..."
5. Choose which one you want to protect - Contents, Objects, Scenarios - or you may leave the 3 items checked, enter a password if you want to and click OK

For versions higher than Excel 2000:
4. Go to "Tools" Menu, choose "Protection", choose "Protect Sheet...",
5. Make sure that the box on the left of "Protect Worksheet and contents of locked cells" is checked.
6. Enter a password if you want and click OK

To protect selective cells in a worksheet:
1. Select all the cells. You can do that by clicking the rectangle on top of row 1 and on the left of column A
2. Right-click anywhere within the worksheet, click "Format Cells" and choose "Protection" tab
3. Make sure that the box on the left of the word "Locked" is not checked. If it is checked, click it to "uncheck" and click OK
4. Select all the cells you want to protect.
5. Right-click anywhere within the cells to choose to protect, click "Format Cells" and choose "Protection" tab
4. Check the box on the left of the word "Locked". (If you also want the formulas you are protecting NOT to be displayed, check the box on the left of the word "Hidden".

For Excel 2000:
6. Go to "Tools" Menu, choose "Protection", choose "Protect Sheet..."
7. Choose which one you want to protect - Contents, Objects, Scenarios - or you may leave the 3 items checked, enter a password if you want to and click OK

For versions higher than Excel 2000:
6. Go to "Tools" Menu, choose "Protection", choose "Protect Sheet...",
7. Make sure that the box on the left of "Protect Worksheet and contents of locked cells" is checked.
8. Enter a password if you want and click OK

Hope this helps.

Submitted by: Joseph D. of Pampanga, PHILIPPINES

**********************************************************************

Answer:

Protecting Cells in Excel

Protecting individual cells in Excel is a nice feature that isn't
immediately obvious and so foils most people who are too busy to spend time
figuring out the answer. Unlike protecting the worksheet, the process of
protecting cells is actually more like selectively UNprotecting cells.

But first, an overview:
Protection in Excel works on three levels:
1. Workbook
2. Sheet
3. Cell

The first two levels of protection are very easy to understand and most
people can activate them without difficulty. To enable protection for your
workbook or worksheet simply select "Protection" from the "Tools" menu and
select the option you wish to enable.

There are a few options you can select when protecting the worksheet or
workbook and you can learn about these by clicking the help question mark
on the dialogue box then selecting the option you want to know more about.
The key option you'll most likely require is "Content" which is on by
default.

Protecting Cells
To protect individual cells there are a couple of extra steps you need to
perform and as mentioned earlier they involve turning protection OFF for
the cells you don't want to protect and leaving it on for those you do.

1. Select the entire worksheet and view the properties of the cells by
selecting "Format" then "Cells" from the menu (You can also right-click and
select "Format Cells..." or use the "Ctrl + 1" key combination).
2. On the "Protection" page of the Format Cells dialogue box you'll see you
have two options; "Locked" and "Hidden". By default "Locked" is ticked on.
Turn cell locking off by removing the check mark. Click the Ok button.
3. Now select the cell(s) you wish to protect and view the "Protection"
page of their properties sheet.
4. Put locking for those cells back on (select the check box) and click the
Ok button.
5. Protect the worksheet as normal by selecting "Tools", "Protection", "P
rotect Sheet..."

Now when you select a protected cell and try to update its content a
message box is displayed informing you that that cell is read-only.

To remove cell protection simply unprotect the entire worksheet.

Regards,

Submitted by: Dave V.

**********************************************************************

Answer:

First, I use MS Office 2000, so these are the step I utilize. Later versions
(beyond 2000) may have slightly different steps.

Now to begin with (saying that you have constructed and saved your
masterpiece) do the following:

1. Start by highlighting the entire worksheet (click on the blank border
cell above the "1" and to the left of "A").
2. Now right click your mouse and chose "Format Cells" (or click "Format"
[on the toolbar]then "Cells"]{the keyboard shortcut is "Alt"+"O"+"E," or
"Ctrl" + "1"}
3. Now click on the tab "Protection.
4. You should see the box "Locked" with a check mark in it, click it and
unmark the box
then click on the "OK" button.
5. Now click on first cell that contains information you want protected (I
would like to say "unmolested," but this is a G-Rated show).
6. Next, holding down the "ALT" key, highlight (that is click on) each cell
you want protected.
Caveat: Be careful, one mistake will require you to start over!
7. With all the necessary cells highlighted release the "Alt" key.
8. Repeat Step 2 (above).
9. Click on the box "Locked." to lock the cells.
10. You have to now protect the worksheet by applying a password (or not).
If you don't, all the work you have just done is for naught.

To protect your worksheet do the following:

1. Click "Tools," then "Protection," then "Protect Sheet" (keyboard shortcut
is "Alt"+T'+"P"+"P")
2. Accept the checked boxes, but you SHOULD enter a password (one you will
always remember) however, you can leave the password box blank.
3. SAVE YOR WORK!!!!!!

And there you have it! A worksheet that anyone can "fiddle with," but not
alter the formulas(without the password, of course), and their end results .

Submitted by: Joe B. of Seattle, WA

**********************************************************************

Answer:

Protecting Cells in Excel

(The instructions that follow are specific to Excel XP, but are very similar
in earlier versions.)

Protecting specific cells from accidental (or intentional!) change is a
two-step process. Imagine every cell in the sheet has a padlock attached to
it. Step One removes the padlocks from certain cells, i.e. the ones you want
people to work on. Step Two turns the key in all the other padlocks, thereby
protecting them.

Step one: Highlight the cells you want to make available to the user. Select
the Format menu and choose Cells. Click on the Protection tab. Click in the
Locked check-box to clear it. Click OK.

Step two: Select the Tools menu. Choose Protection, Protect Sheet. Choose
which items you want to allow users to modify. (Earlier versions only
allowed a few choices, XP offers a long list). Set a password for the
protection. This is important - if someone tries to change a locked cell,
Excel will provide a helpful message which tells them that the cell is
protected but also how to remove the protection. If they don't know the
password, they can't do it.

An additional feature found in the Protection tab of the Format Cells menu
choice is a check-box for Hidden. If you click in this to activate it, those
cells will not show their formulas on the Formula Bar when selected after
you have turned on Protection.

I hope this helps.

Regards,
Submitted by: David O. of Crawley West Sussex, England

**********************************************************************

Answer:

To protect cells so that they cannot be changed, you must protect a
worksheet. By default, all cells are locked in Excel. So the first step
you need to perform is to select the cells you want users to be able to
change when they are in the workbook. To unlock the cells:
1. Click on the Format menu.
2. Click on Cells.
3. Click on the protection tab in the Format Cells dialog box and clear
the Locked check box.

Now you need to protect the worksheet/workbook. To do this:
4. Click on the Tools menu
5. Highlight the Protection submenu
6. Click on Protect Sheet or Protect Workbook
7. Click OK (A password is optional).

After you perform these steps, users will not be able to change formulas or
values in the locked cells. If the workbook is going to be shared among
many users, you can select Protect and Share Workbook. In this dialog,
Excel gives you the option to turn on change tracking which allows you to
see who changed which cell last. To unprotect a worksheet or workbook,
follow steps 4 through 7 above selecting the option that starts with
Unprotect to re-enable the locked cells.

Submitted by: Chris S. of Tucker, GA USA

**********************************************************************



Answer:

ORIGINAL QUESTION: In Microsoft Excel, how can I protect selective
cells in a worksheet (the ones that contain formulas) from accidentally
being altered or deleted?

Excel by default locks all cells in a workbook; however, it does not
protect each sheet so this is a two part process. You need to first
unlock the cells you want a user to edit and later protect the sheet to
allow it to work like you intended. Your first step is to build the
spreadsheet the way you want it to with the formatting and formulas
necessary to calculate what you need.

Next, you'll have to select and unlock the cells you want to allow the
user to edit (those without formulas). The easiest way to do this is to
hold down the Ctrl key on your keyboard while you click on each cell or
range of cells you would like the user to change once the workbook is
finished. After you have selected the cells go to Format/Cells and
select the Protection Tab. Uncheck the box Locked which will unlock the
selected cells for editing once the sheet or workbook is protected.

Finally, go to Tools/Protection and choose Protect Sheet. You can
enter a password or leave it blank to test it out. I usually enter a
password once the sheet is tested and working like it should. Then
click OK and you're all set.

You will notice that once the sheet is protected, you can use your TAB
key to move through each editable cell. This makes it easy for users to
get to areas that they need to enter data.

Two other notes, if you do format the cells be sure you consider the
printing size and display needs of your end user as they will be unable
to change any formatting in the sheet once it's saved. For example, if
you're allowing an end user to enter names, make the cells large enough
to accommodate the longest name in your organization, First, Middle, and
Last.

If you're allowing a user to enter a value, be sure you consider how
large a value you want to allow the spreadsheet to display. For
example, if it is for a widget count, perhaps 4 digits are enough and
decimal places are not necessary whereas for a real estate office sales
figure, you may need to make the cell large enough to accommodate
millions of dollars and include decimal places.

Submitted by: Mike W. of Riverside, CA, USA

**********************************************************************

Answer:

In order to prevent certain cell(s) in your worksheet from being modified, you need to lock these cells. But locking cells alone has no effect unless the worksheet is protected, i.e. the Protection feature is activated.

By default, all cells are locked. So if you activate the Protection feature straight away, the entire worksheet will be protected. This is not exactly what you want because you want to be able to input/key in numbers in certain cells while letting the formulas in the other cells to automatically do the calculation. Thats the whole point of working on a spreadsheet like Microsoft Excel.

So to override the default and unlock cells, highlight the entire worksheet by clicking the upper left corner box (where column headings and row headings met).

Then go to Format menu and choose Cells. In the Format Cells dialogue box, choose Protection. Under Protection, there will be two choices; Locked and Hidden.

As I mentioned before, by default, all cells are locked. So please untick the Locked box and then click OK. Now all the cells in your worksheet are not locked.

Now highlight only the cells you want to protect. Repeat the above step; Format; Cells; and Protection. Then tick the Locked box. Your selective cells (containing formulas) are now the only cells that are locked.

Finally activate the Protection feature which I mentioned earlier in the first paragraph. To activate it, simply choose Protection from the Tools menu, and then choose Protect Sheet. A password is optional. Click OK.

Now only your selective cells containing the formulas are protected while the rest can be modified, i.e. entering data, writing text etc.

If later, you find out that your formula is slightly not right and you need to modify it, simply deactivate the protection feature, do the necessary changes and then activate the Protection feature again.

Submitted by: Rusman B. of Petaling Jaya, Selangor. Malaysia