locking formulas

S

speary

Is there a way I can lock my formulas so no one can change them when i
send my spreadsheet out? I have about 50 cells with formulas in them in
4 or 5 columns and I need them all locked. Thank you for any
suggestions.
 
C

Connie Martin

Select all cells that DO NOT require to be locked, go to
Format>Cells>Protection and remove the check mark beside "Locked". Click
OK. Then go to Tools>Protect Sheet> and check all boxes and put in a
password. This way people can type in cells unprotected (filling out forms,
etc.) but cannot alter cells that have been left locked. However, you must
password protect the file for this to work, and you must do it in this order.
Hope this helps. Connie
 
J

JE McGimpsey

You don't need to password protect the file, just the sheet.

Note, however, that this will only provide protection from inadvertent
overwriting, not any security. If someone has the gumption to find these
groups, they'll be able to bypass the Worksheet Protection in about 30
seconds. See

http://www.mcgimpsey.com/excel/removepwords.html
 
D

dominicb

Good afternoon Speary

You need to use the Tools > Protection > Protect Sheet option.

Bear in mind that this will protect any cells that are marked as
"Locked", and by default ALL cells in a new worksheet are locked. To
unlock them select all the cells that your users are to be able to
change and press CTRL + 1, Protection and uncheck the Locked box. You
need to do this before you invoke the protection.

One big caveat : always bear in mind that no form of protection offered
by Excel is foolproof - the protection above being particularly flimsy.
It will protect your formulae from the casual user, but anyone
determined enough can remove the password protection in seconds.

HTH

DominicB
 
S

speary

Thanks I am going to give that a try in a minute the protection is more
to keep people from inadvertently deleting the formula to calculate
something.
 
C

Connie Martin

Yes, I meant to say "protect the sheet". Thank you, too, for the combination
to break into the safe!! I always heard there was a way to do it but had
never searched it out!

Connie
 
G

Gord Dibben

speary

By default when a work sheet is protected all cells are locked.

Select cells in which users are to enter data.

Format>Cells>Protection. Uncheck the "locked" option.

Now Tools>Protection>Protect Sheet.

Set the options you wish users to have then provide a password and OK.

Note: sheet passwords are easily cracked but the locking method will protect
the formulas from inadvertent erasure or change.


Gord Dibben Excel MVP
 
Top