Why are my formulas changing to text if I edit them in any way?

Copper Contributor

When I first enter a formula into a cell, Excel calculates and displays the result of the formula, but If i go back and edit the formula, Excel simply displays the formula as text.  At this point, even if I delete the edits, the formula will not calculate.  I can only get it working again by using Undo to back up to my original formula.

It can even be as simple as a pointer to another cell,

eg.  =C1   will display the contents of C1.  If I edit it to say =D1, the cell will simply read "=D1". If I change the D to a C, it will still just read "=C1".  If I Undo back to the original =C1, it will show the contents of C1

It is not simply just displaying the formula, because any links back to that cell become broken.  I have played with data types and Formula calculation options...I cannot figure this out.


Please help...I cannot make any edits.

2 Replies

@GerryVB  wrote: ``If I edit it to say =D1, the cell will simply read "=D1"``


To be clear, you are saying that the cell displays =D1 even when the cell is not selected, not just in the Formula Bar.  Right?


If so, you probably formatted the cell or column as Text after you had entered the original formula, =C1.


Select the cell or column, and format it as General (or any other numeric format; not Text).


It is not necessary to format a cell as Text if a formula returns text.


And it does not help to change the format of a cell format to Text after you calculate a numeric result.  The type of the result remains numeric, despite the cell format.


The only time that we should format a cell as Text is when later, we enter data that we do not want Excel to interpret as numeric or a formula.

@Joe UserOK, I figured it out!

I was going in after I edited the formula (and it would only show the formula as text in the cell) and changing the datatype to general.  But it is too late by then...  I have to change it to general, then make an edit, then change the format again after, if I need to.