In Excel how do you clear data without losing a formula?

[CalifGranny]

Older version of Excel: is there a way to clear the contents of a cell
without deleting the formula (not the formatting, the formula).

Thanks.

 

[Dave Peterson]

Nope.

The cell can contain a formula or a value. Once you clear a cell, you've
cleared everything.

But maybe you can change the formula so that it looks at some sort of key and
makes the cell look blank if something isn't the way you like it:

=if(a1=1,"",vlookup(a17,sheet2!a:e,4,0))

So if I put a 1 in A1, then the cell with this formula looks empty.

If I clear A1--or put something else in it, then I'll see what my =vlookup()
formula would return.

 

[Gord Dibben]

Depends upon the formula.......

If the formula references other cell(s) you must clear the cells the formula is
referencing.

Select all cells and F5>Special>Constants.

Check or uncheck your options(uncheck "text" if you want to leave titles etc.)
and OK

Edit>Clear>Contents

If the formulas are not referencing other cells then you would be stuck with
just deleting the formulas to clear contents.

i.e A formula like such =123*42 would have to be deleted or changed manually.


Gord Dibben MS Excel MVP

 

[CalifGranny]

Nope.

The cell can contain a formula or a value. Once you clear a cell, you've
cleared everything.

But maybe you can change the formula so that it looks at some sort of key and
makes the cell look blank if something isn't the way you like it:

=if(a1=1,"",vlookup(a17,sheet2!a:e,4,0))

So if I put a 1 in A1, then the cell with this formula looks empty.

If I clear A1--or put something else in it, then I'll see what my =vlookup()
formula would return.

A bit beyond me, but thanks for the "nope", saved me a lot of time!

 

[CalifGranny]

:
Thanks! I'll make up a dummie sheet and try that: appreciate your time.
Happy New Year.