Wednesday, August 06, 2008

Value of Excel Formula Won't Show

This problem has driven me nuts on more than one occassion, so I am documenting the solution here for reference. Maybe this will help you.

Problem: Enter formula into cell such as "=B2". After entering formula, instead of seeing the value of the B2 cell, you see "=B2".

Step 1: Go to "Tools" menu, select "Options...", then select the "View" tab. Under the "Window options" section, is "Formulas" checked? If so, uncheck it, click "OK", and hopefully you're done. If that doesn't fix the problem, or if "Formulas" is unchecked, then go to step 2.

Step 2: Change the number format of the cell to "General". If this doesn't fix it, then copy the text of the formula, paste the text in Notepad, delete the cell, change the format of the cell to "General", then paste the text from Notepad into the cell. That should fix it.

21 Comments:

At Thursday, August 07, 2008 9:16:00 AM, Anonymous Anonymous said...

Hi Rick,
I sometimes cause this to happen accidentally when trying to key in Ctrl+1 and inadvertantly hitting Ctrl+` which is to the left of the "1" on my keyboard. The Ctrl+` command toggles between showing the formula and the formula result.
Hope that works for you.
PA, Todd

 
At Thursday, August 21, 2008 6:55:00 PM, Blogger Richard Upton said...

Thanks Todd. I didn't know about that shortcut for Step 1. Sometimes Step 1 won't work and Step 2 is required.

 
At Thursday, January 29, 2009 10:21:00 AM, Anonymous Anonymous said...

Thanks a bunch Richard for posting this. I had to refresh the values in over 10k cells. You saved me.

 
At Thursday, March 12, 2009 6:14:00 AM, Anonymous Anonymous said...

Ah, I've been struggling with this for a solid 24 hours. Solution number 3 (cut and paste) seems to work. Hallelujah! Thank you for posting this!

 
At Sunday, April 05, 2009 12:45:00 PM, Blogger MikeCraig said...

Another solution is to insert an apostrophe (') in front of the equal sign (comments it out and explicitly shows the formula). Hit return and then go in to the cell and delete the apostrophe (hit return). This then shows the value instead of the formula. Why this works or why there is the problem in the first place I don't know.

 
At Monday, May 11, 2009 12:04:00 AM, Blogger nathan said...

Thanks, this post helped me. Such a silly thing.

 
At Monday, March 29, 2010 3:40:00 AM, Anonymous Anonymous said...

Thanks a LOT! It turns out that we should always convert the cells into general numbers!)))

 
At Wednesday, May 12, 2010 8:44:00 AM, Anonymous Anonymous said...

Saved me! Thanks.

 
At Friday, June 18, 2010 2:25:00 PM, Blogger geocode100 said...

I've had success entering the formula in a cell that DOES return the formula value (instead of the formula as text)and then dragging to the inoperable cell (answer "yes" to the prompt asking if the destination cell should be replaced). I commonly need to repeat a LOOKUP formula in cells below. If this is the case, I then use the auto fill function to populate columns.

 
At Monday, November 08, 2010 8:39:00 AM, Blogger Zina said...

OMG the apostrophie (sp?) trick worked for me!

 
At Monday, November 08, 2010 8:40:00 AM, Blogger Zina said...

omg the apostrophie trick worked for me thanks!!

 
At Wednesday, March 16, 2011 2:04:00 PM, Blogger Alex said...

I've heard about some programs for work with ms excel files. But one of them liked me more. It showed very impressive facilities and forced to believe in all its resources for working out most every trouble for some minutes - view xlsx.

 
At Tuesday, April 26, 2011 10:15:00 AM, Anonymous Anonymous said...

This is very helpful. I'm constantly fighting this when using Vlookup. THANKS!

 
At Thursday, November 17, 2011 8:51:00 AM, Anonymous Anonymous said...

This issue occurred because those cells were initially format as text and so will just display whatever text was keyed in.
Changing the cells' format to General will fix the issue. Adding and deleting the apostrophe is basically changing the cell from text to general too.
Hope this clarify.

 
At Monday, January 02, 2012 7:52:00 AM, Anonymous Anonymous said...

Thanks, step 2 helped.

 
At Wednesday, January 04, 2012 10:26:00 AM, Anonymous Anonymous said...

Change cell format to General, then click in the cell to get the cursor exactly to the left of the = sign (this should be the farthest left you can go in the formula bar), then hit backspace twice, then enter

 
At Wednesday, October 17, 2012 1:55:00 AM, Anonymous Manos said...

Thank you very much. Step 2 worked for me.

 
At Wednesday, October 17, 2012 1:56:00 AM, Anonymous Manos said...

Thank you very much. Step 2 worked for me.

 
At Tuesday, March 05, 2013 9:06:00 AM, Anonymous Anonymous said...

step 2 worked for me too (well after I double clicked in the cell after changing format to General) Thanks

 
At Thursday, March 28, 2013 8:18:00 AM, Anonymous Anonymous said...

Step 2 worked for me as well !

 
At Thursday, May 09, 2013 11:25:00 AM, Anonymous Anonymous said...

Much to my frustration, I've tried all of the tips metioned here with great hope but none of them work. I can see the results of my formula in the cell, but when I click the cell into edit the result, it takes me into the formula and will only allow me to edit the formula not the result. I have similar lookup formulas in other spreadsheets that do not behave this way. I have also copy and pasted those in without success. Any other ideas I can try? (I'm in Excel 2010) Thank you

 

Post a Comment

<< Home