Forum Discussion
Formulas displaying as text
Can anyone tell me why my worksheet allows me to type in formulas one minute but then suddenly starts to display the formulas as text and won't do the calculations?
Hi, Uncheck show formula option under Formulas Menu
- I had the same problem. After I had all my values in the cells, I highlighted the entire column and clicked general again and it worked. Hope that helps. ... Carl
- For me, everything was set properly. Format was "general" and calculation options were "automatic". (btw, #2 and #5 in the suggestion are the same) For me, I missed a trailing parenthesis, but Excel didn't show an error like it usually does. I don't know why. So it was #4, "Errors in the forumula".
I was frustrated yesteday. Finally, I recreated a new column, and then followed instructions. And results showed. I then deleted the original columns
- This is frustrating. It has happened to many times, and I do create a new column as above, or sometimes I re-enter on another row of the same column and it works. I think there is some kind of non-printable character gets into the formula.
- Check your formatting. In my case, when I make new rows or columns, sometimes it copies the formatting of the neighboring row/column and formats everything as TEXT (because that's what the previous column was set as). That's what breaks my formulas, until I catch it.
tbrien1985 I have been experiencing exactly this and it's driving me mad! I have tried all the noted resolutions but still happening and even shutdown and rebooted laptop but this issue won't go away!
There are a few possible reasons why Excel might be displaying formulas as text instead of calculating them:
- Number formatting: Make sure that the cells containing your formulas are formatted as numbers, not as text. If the cell is formatted as text, Excel will treat any formula entered into that cell as text, and will not perform the calculation. To change the cell format, select the cell(s), right-click, and choose "Format Cells". In the "Number" tab, select "General" or another number format.
- Calculation options: Check that Excel is set to automatically calculate formulas. To do this, go to the "Formulas" tab in the ribbon, and click on "Calculation Options". Make sure that "Automatic" is selected.
- Show Formulas mode: Check if you have accidentally turned on the "Show Formulas" mode. This mode displays the formula in each cell instead of the calculated result. You can toggle this mode on and off by pressing "Ctrl + ` (grave accent)".
- Errors in the formula: If there is an error in your formula, Excel will not be able to calculate it. Check your formula for typos, missing or incorrect arguments, or invalid references.
- Calculation settings: If the calculation settings are set to "Manual", Excel will not calculate formulas automatically, and will display them as text. To change this, go to the "Formulas" tab in the ribbon, and click on "Calculation Options". Select "Automatic".
If none of these solutions work, try restarting Excel or your computer. If the problem persists, there may be an issue with your Excel installation, and you may need to repair or reinstall Excel.
Hope it helps you!
Thank you Niko,
It was item 3 that fixed it for me. I had been using a keyboard that very ocasionallly switches modes (HP laptop), in particular the tab key does wierd stuff and it had somehow activated 'Show Formulas'.
Your answers saved me a lot of work looking for a solution to a problem that it was difficult to describe with accurate search terms!
I appreciate your time.
NikolinoDE Thank you for the information. The one option I found was while walking through the formula wizard it did not include the space after the comma and this made the formula unusable. Once I added the space after the comma / in front of the next argument, the formula worked.
Thanks for the list! In my case, your #1 option was the issue. I think it usually happens when I add another column to a table, and it copies the "Text" formatting from the previous column without my realizing.
I appreciate the help!
