Unwanted Formula Display

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

Occassionally I will get to a point where after editing a
cell, the formula will be displayed instead of the value.
I've not done anything other than the edit (say make the
reference absolute). The value is displayed, the cell
edited, the forumula gets displayed. I can't seem to
revert to diplaying the value. This result is cell-
specific. I don't see where Format changes this. Any help
in resolving this would be appreciated.

Thanks.
 
Hi,

Normally this happens if the cells formatting is changed to text. You
should change cell formatting back to general (or what it should be,
but not text) and then re-edit the formula (F2, Enter)

I don't know how this could happen by itself. Never happened to me
though.

- Asser
 
Are you sure you didn't accidentily delete the "="?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Thanks. That seems to be the major issue though something
else is going on. Can't seem to get the change to General
to stick consistently. Even a cell that has been changed,
when copied results in the copied cells having the Text
characteristic. Results in lots of back and forth editing.
Also seems to apply only to external references.

Also (and this is why I say something else is going on),
just edited a cell and it returned a value formated to 15
decimal places! I know this wasn't the original format nor
one I ever would have used. Required me to manually reset
the format to the desired format.

Thanks again.
 
Excel likes to help you with the numberformat for the cell.

Try this on a test worksheet.

Put the date in A1
put =a1 in cell B9
B9 inherited that format.

Now try this:
format A1 as Text
Type something in it.
B9 reevaluated its formula.

Now edit B9. (F2 and enter is fine, maybe twice)

It shows the formula--That's excel trying to help by applying that format from
the "sending" cell.

The only way I know to deal with it is to just fix it. Format the formula cell
as general (or whatever you want) and hit F2|enter. (Then stay away from that
cell!!!)

Sometimes, it's helpful. Sometimes, it's a pain.
 
Jonathan

The only way I have been able to resolve this issue is to "clear" the
cell. Clear/All will get rid of all the hidden format you can not see.
Delete only "deletes" contents of cell. Formatting to general does
not always work.

Clear will allow the formula to display value.

As with all Microsoft products, things do not always work the same way
twice or as originally intended. This is why forums were invented.
 
Next time it happens to you, try formatting as General, then hit F2 followed by
enter.

Excel will then see it as a formula.
 
Back
Top