Lock formulas only not data

[Terry]

I would like to protect, lock or keep someone from accidently deleting a
formula in Excel 07. I do not want to password protect the workbook or
worksheet.
We need to enter data into the cells and then select most of the worksheet
all at once and hit delete, when the data is deleted I do not want to lose
the formulas. Can this be done?

 

[JLatham]

Tough question. First, it's not necessary to assign a password to protect a
worksheet. Worksheets can be protected without a password. That keeps
accidental changes to formulas from happening unless someone unprotects the
sheet.

That kind of setup may work for you, however if there are any cells that are
set to "Locked" in the area you do select to clear out, then you'll get an
alert saying that you're trying to change protected cells. But it would work
find as long as the area(s) you select don't have any protected cells in them.

Typical way of setting this kind of thing up is to first select all of the
cells on a sheet and then using Format | Cells | Protection and clear the
"Locked" option. Then you go back and select just the cells you don't want
to be changed (those with formulas in them) and use Format | Cells Protection
and check the "Locked' option. Then use Tools | Protect sheet and simply do
not enter a password.

Hope this helps some.

 

[Terry]

Thanks I'll give this a try

Tough question. First, it's not necessary to assign a password to protect a
worksheet. Worksheets can be protected without a password. That keeps
accidental changes to formulas from happening unless someone unprotects the
sheet.

That kind of setup may work for you, however if there are any cells that are
set to "Locked" in the area you do select to clear out, then you'll get an
alert saying that you're trying to change protected cells. But it would work
find as long as the area(s) you select don't have any protected cells in them.

Typical way of setting this kind of thing up is to first select all of the
cells on a sheet and then using Format | Cells | Protection and clear the
"Locked" option. Then you go back and select just the cells you don't want
to be changed (those with formulas in them) and use Format | Cells Protection
and check the "Locked' option. Then use Tools | Protect sheet and simply do
not enter a password.

Hope this helps some.

 

[Max]

And if you wish to quickly select only all formula cells on the sheet for
some action (formatting, protection, etc):

Press F5 > Special > Check "Formulas" > OK

---

 

[Terry]

Thanks

 

[Max]

Welcome, Terry