Microsoft Excel
Tutorials
Hope is seeing yourself
where you want to be.
How to protect cells in Excel XP and Excel
2003
to prevent users from editing formulas.
(Excel 2007 is fundamentally similar,
although the user interface has changed !)
After you’ve worked so hard to make your formulas work,
why let the user inadvertently (or intentionally) change them?
(They may not realize the consequences on other
calculations when changing them ?)
Also if they are allowed to edit them, and they don’t
work anymore,
guess who
will be blamed for the results ?
By default, all cells are initially locked,
(which does NOT mean that
they are “protected”)
although
you wouldn’t know this until you try to “protect” cells.
For reasons known only to Microsoft,
this is a
fragmented, two-step process
with the
steps located in different unrelated places.
Step 1 is to “unprotect” all except the cells you want
to protect.
Makes sense so far, huh ?
Step 2 is to protect the entire sheet, except those
that you have selected.
Beginning to make sense ? I
didn’t think so.
So, here is how to do it :
Select cells that are to be made available.
This means all of the cells that you DO NOT want to
protect.
These would be the cells that contain data the user is
going to enter.
Use <Ctrl+Click> or
<Shift+Click> to
select all of the cells that
the user CAN change
before continuing.
Then, from the Format
menu,
select the Protect
option,
and then, uncheck the "Locked"
checkbox.
This is the dialog box in Excel 2003 and
XP and earlier versions :
From the Tools
menu,
select Cells
from the Format menu,
then select the Protect
Sheet tab :
This action allows the user to select only the “unlocked” cells.
This gives access only to the cells that you have NOT
protected.
Un-check the “Select
locked cells” box to prevent users from selecting the cells you’ve
protected.
This prevents users from accessing the cells that
contain your programmed formulas.
You might want to bookmark this page,
because you’ll probably forget how to do this.
(It’s why I wrote this page in the first place !)