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:

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

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.

Anonymous said...

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

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!

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.

nathan said...

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

Anonymous said...

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

Anonymous said...

Saved me! Thanks.

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.

Zina said...

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

Zina said...

omg the apostrophie trick worked for me thanks!!

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.

Anonymous said...

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

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.

Anonymous said...

Thanks, step 2 helped.

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

Manos said...

Thank you very much. Step 2 worked for me.

Manos said...

Thank you very much. Step 2 worked for me.

Anonymous said...

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

Anonymous said...

Step 2 worked for me as well !

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