This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a spreadsheet with many formulas. When I go into a cell to make a modification in a formula, Excel automatically changes the format to text and the formula no longer operates as a formula--I just see the text in the cell. I know how to fix that one instance: I change the format back to "General" and then to make it take effect, I have to delete the "=" at the beginning of the formula and they type the "=" back in. This is a very laborous process when I have 30 or so formulas to change. How do I stop Excel from assuming that my formula is supposed to be text just because I changed a number in the formula?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
Try to coerce a calculation on formula driven cells via the following:
This will recalculate all formulas on the sheet.
Hope this helps.
Answer accepted by question author
Deb wrote:
I have a spreadsheet with many formulas. When I go into a cell to make a modification in a formula, Excel automatically changes the format to text and the formula no longer operates as a formula--I just see the text in the cell. I know how to fix that one instance: I change the format back to "General" and then to make it take effect, I have to delete the "=" at the beginning of the formula and they type the "=" back in. This is a very laborous process when I have 30 or so formulas to change. How do I stop Excel from assuming that my formula is supposed to be text just because I changed a number in the formula?
First, in order to "make it take effect", all you need to do is select the cell, press F2, then press Enter (or ctrl+shift+Enter if it is an array-entered formula).
But of course, that is still laborious, and it does not address the primary problem.
I think some information is missing or has been misstated.
Normally, the only way I know of for that to happen is if you change the cell format to Text first, then edit the formula. I am not aware of any circumstances like your description when Excel will change a cell format from General to Text automagically; not even any numeric format to Text.
However, Excel might change the cell format to Text under some circumstances when you import data; perhaps even when you copy-and-paste data, although I don't believe so.
And the cell format might be changed by macros, notably an event macro.
If none of those circumstances apply, I suggest that you try the following steps in an effort to isolate the culprit.
Start Excel in safe mode, be sure that macro security is disabled (see the Trust Center), then open your Excel file in "safe" Excel. Does the problem go away?
To start Excel in safe mode, press and hold down the Ctrl key while you click on the Excel program icon, not the Excel file icon. Continue to hold down the Ctrl key until you are prompted for yes or no to continue in safe mode. Then release the Ctrl key; and of course, click on Yes.
If you continue to have a problem even in safe mode, I suggest that you upload an example Excel file to a file-sharing website and post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here. The following is a list of some free file-sharing websites; or use your own.
Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
These answers were so useless it made me (an impatient, busy person) take time to register and reply for this poor lady. I couldn't even find an answer on 5 other forums!
Deb, just change all the cells that the formula is referencing to general.
I had this same problem just now using the SUMIF formula. My sum range was formatted to general but my criteria range was text. The all-knowing Microsoft programmers assumed we users can't decide for ourselves what format we want so they made it automatically update to the format of the referenced cell.
We have been putting up with this problem for WAY TOO MANY YEARS. The problem is built into the General format spec. It "helps" the user by morphing into the format of the cells being referenced by the formula. The idea is that the General Format is supposed to be smart enough to guess what format the user wants, and then convert itself into that.
Cells containing formulas are usually formatted General. If your formula operates on cells containing text strings, everything works ok when you first enter the formula, but when you press F2 to edit the formula and then press ENTER, the General formatting spec dutifully changes itself from General to Text format, thus disabling the formula and converting it into a mere text string. To fix this, you must manually set the format back to General, and hit F2, Enter, to re-enter the formula.
The answer would be for Microsoft to add a new format spec, called "Formula", that would not change itself into something else. Or, make the General spec smart enough to remain General under these circumstances.
This was also very frustrating for me. The reason for my issue was that the cells that it was using to calculate were showing as numbers but when I dug into those cells they were formatted as text and once I formatted them as general my formulas stopped changing. I am not sure if this is your error but take a look.