Below are the top 5 most common reasons why formula text is shown in cell instead of its results. They are sorted by frequency with most popular at the top.
They apply to Excel 2013 and Excel 2016.
No equal sign before the formula
Make sure that there is an equal sign before the formula.
Formula is inside quotes or apostrophes
Check that you typed formula with no quotes or apostrophes at beginning or end.
Worksheet is set to display formula – not results
Hold down theCtrl and push `
or
Click on the Formulas Tab then check if “Show Formulas” button isn’t on.
Cell is formatted as text
Sometimes showing formula as text might be due to cell format. Change it to general to see if it helps.
-
Right click on the cell with formula
-
Choose Format Cells
-
Select Category -> General
-
Click OK button
Cell with formula is locked
Found that the cell I had the formula in was “locked” (Format Cells / Protection) – it would seem that this also prevents the value from showing.
De-selecting “Locked” the value appeared immediately.
(tip from our reader Clare – thank you!)
I know how to fix it back, but why is Excel changing my cell formats? This has been happening in various spreadsheets for the last year or so:
I enter a formula, it works fine. Then I edit the formula, which causes Excel to reformat the cell as text. I then have to fix the format, and copy and paste my formula back into the cell to get it to show correctly. Way too many steps to making one minor edit to a formula.
Is there a default setting that needs to be fixed? I’ve been using Excel since the late 90s, haven’t had this trouble until recently.
Does this happen in csv files but not xls? Any insight would be helpful.
It was helpful!
Thank you So Much
thanks
thanks a lot in my case I was a victim of the accidental press of Ctrl and the key beside 1. freaked out a bit and this post helped me out of my nightmare. thanks again.
I had to DELETE the cell and recreate it.
Thank you! Never would have found the show formula button without this guide (I can’t even imagine how it got turned on in the first place).
thanks for good information…..
thanks for good information
Thanks so much.
It’s help me a lot.
Thank you sooo much. This explication helped me to solve my problem with in a second.
hey thanks for the solution
This is such a random, critical and frustrating issue that it seems Microsoft should have supported by now! Thank you to everyone who shared solutions! The solution that worked for my system was posted by Jira Oct 2017 explaining to find all “=” and replace them with “=”. I literally tried every tip previous and they failed for me.
It was that worked for me!!!
Thank you! Finally that worked for me.
Hi, thank you for your post. I found my problem quickly returned to my work.
Hi,
Thanks a lot for the article.
as I found that doing this will help
1- delet the cell content
2- Change the cell type to General
3- Rewrite the formula
Enter it shall work if other solutions didn’t
The only thing that worked for me was F2 hold and press enter, after exhausting all other possibilities
Agreed – this is the only thing that worked for me
I found that copying the formula, pressing “CLear All” and repasting the formula worked.
This was very helpful. Thank you.
Perfect, I knew I hit something but I couldn’t remember what I did. Ctrl+` does it every time. 🙂
Tried all these things and it still didn’t work.
Fudged around quite a bit and found that if you imported (copied) a tab (we’ll call this tab A) from another excel spreadsheet, then, called (used “=” sign) from a cell on tab A, it would display as the formula even if you did all the steps listed above.
The solution I found was to just copy and paste (as values) all the information on the tab A into a brand new tab (We’ll call this tab B) on my own excel spreadsheet. Then, call from the values in tab B instead of tab A. This fixed the issue. Wonder what the issue is, since the Excel version of tab A and my excel spreadsheet are exactly the same?
Didn’t find this solution anywhere while searching online so, I hope this helps somebody else that is having the same problem as I had.
thank you for sharing!
I had same problem and simple solution was to find all “=” and replace them with “=”. 🙂
EXCEL recalculated all cells and I got desired result.
If excel shows formula instead of value then while pressing ” F2 ” tab hit enter.
Thanks for the info provided which was not even provided by Microsoft support!
I found that mine was formatted for text. I changed that, but nothing happened.
I clicked into the formula, then hit enter and that is when the value finally showed up.
thank you for sharing this information. I have updated the article and included tips from you and Clare.
Hope this will help other people!
This worked for me! Thanks.
Hi – Great tips.
I tried all of them but it still didn’t work.
Found that the cell I had the formula in was “locked” (Format Cells / Protection) – it would seem that this also prevents the value from showing.
De-selecting “Locked” the value appeared immediately.
Clare
thank you for the information, will add it to the article!
Thanks,
Clearly explained..
it helped me
Thanks it solved my problem… 🙂
I struggled with this often and went through all of the above many times with no luck. I often just type in my functions and finally noticed that if I used lower case for the first function name it would not display correctly. When I started making sure they were upper case the problem was corrected.
thanks for the info!
Try putting TRIM() around your formula
Thanks for the Trim work around. I worked after everything else failed. 🙂
TRIM finally helped; thanks!
This worked for me as well !! Thank you
I have a similar problem, i am importing text from one cell to another using ‘=AO’ in the cell. Worked yesterday on one worksheet and works on only a couple of cells on this one, no indication why? any ideas?
have you checked all possible reasons from the post?
This is the only article that helped me. THANKS