How do I clear a cell contents but not the formula behind it

[Guest]

I have been asked to look at a pronblem with a colleagues spreadsheet in
Excel. When you clear data within a cell, so new data can be added the
formula for teh cell has also been deleted.

Is there a way to set cells to maintain underlying formulas by default?

 

[Pete_UK]

The cell will be locked by default, but you need to protect the
worksheet for this to take effect - Tools | Protection | Protect
worksheet. You have an option to provide a password if you wish, but
if you do then write it down somewhere so you don't have to come back
here asking how to unprotect it having lost your password !!

This will prevent accidental erasure of the formula.

Hope this helps.

Pete

 

[Guest]

Protecting your formula cells as suggested by Pete_UK will protect your
formulas.

If you want to suppress the formulas from displaying 0's (zeroes) when the
source data has been removed you can do that by clicking on TOOLS/OPTIONS.
Click the VIEW tab and towards teh bottom of the tab page there are 3 columns
of check boxes, remove the check from the check box labeled ZERO VALUES
(located in the center column at the bottom) to suppress 0's from displaying.

 

[Guest]

I'm not sure why you would delete data in a cell that you have a formula in?
You typically would delete/over write the data in the cells *providing* the
data to the cell with the formula.

I do have a similar situation like this with our data collection gear. I
need to get the data provided by the formula(s) out before overwriting the
data supplying my formula cells. I do that by copying the data provided by
the formula cells and then Pasting Special / Values to a new sheet. Now the
copied data is static having left out the formulas and I can over write the
cells providing data to my formula cells.

I hope this helps.

 

[Guest]

SliderSteve

Because you don't normally enter data into a cell that also has a formula,
this will work to clean all the old data out leaving only the formulas for
new data. Before you do this PLEASE back up you worksheet!!!

Select your entire worksheet(or the area you want to
cleanup)>Goto>"Edit">"Go To">"Special">"Constants">Select all that Apply>"OK".

Now all is hilited just delete the contents (I prefer right click and select
Clear Contents) Your worksheet is now ready for the new data, with all
formulas still in place. Be sure and read and follow the other advise you've
recieved it is good stuff to do.

Mike Rogers

 

[Gord Dibben]

You can have a formula in a cell that returns results from other cells.

Or you can a have a value in a cell. Not both at once.

See if F5>Special>Constants>OK does what you want.

This will select cells that aren't formulas.

With those cells selected, Edit>Clear Contents, leaving your formulas behind
waiting to return new results.


Gord Dibben MS Excel MVP