Excel Formulas are not working?!? What to do when all you see is the formula, not result
Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. The formula shows up in the cell instead of the result, like this:
Now what to do?
Of course, you can be careful when eating donuts. But careful donuts sure sounds like a paradox. So instead lets roll up our sleeves and find out the reason for this mishap.
The top reason for formulas are shown as formulas instead of results:
You may have accidentally pressed CTRL+` (back quote symbol, the key below escape key in your keyboard) or activated the “Show Formulas” mode in Excel.
When you do it, excel shows the formulas instead of their results.
To fix this error and get back the values (or results) just press CTRL+` again.
The next reason why formulas are shown as formulas:
You may have set the cell formatting to “Text” and then typed the formula in it.
When you set the cell formatting to “Text”, Excel treats the formula as text and shows it instead of evaluating it.
To fix this error, just select the cell, set its formatting to “General”. Now edit the formula and press enter. (Alternatively you can press F2 and then Enter after setting format to General).
The less probable reason why formulas are shown as formulas instead of values:
You may have accidentally typed a single quote ‘ before the = sign in the formula.
When you type single quote ‘ in a cell excel treats the cell contents as text and does not evaluate any formulas within.
To fix this error, just remove the single quote.
What is your experience with excel formula errors?
The very first time I pressed CTRL+` by accident, it nearly freaked me out. All the columns became too wide and the formatting went for a toss. Everything looked weird. It took me a while to figure out that I accidentally pressed the Show Formulas shortcut (CTRL+`). I felt huge relief when I got the results back.
What about you? Did the formula error ever freaked you out? What other things about formulas worry you? Pls. share using comments.
More on Formula Debugging:
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]||Speaking at TechEd 2010 on “How to Select the Right Chart for your Data” »|