Rick Upton's Grab Bag
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.
This is my personal blog which covers a grab bag of topics that aren't covered by my other blogs.
20 comments:
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
Thanks Todd. I didn't know about that shortcut for Step 1. Sometimes Step 1 won't work and Step 2 is required.
Thanks a bunch Richard for posting this. I had to refresh the values in over 10k cells. You saved me.
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!
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.
Thanks, this post helped me. Such a silly thing.
Thanks a LOT! It turns out that we should always convert the cells into general numbers!)))
Saved me! Thanks.
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.
OMG the apostrophie (sp?) trick worked for me!
omg the apostrophie trick worked for me thanks!!
This is very helpful. I'm constantly fighting this when using Vlookup. THANKS!
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.
Thanks, step 2 helped.
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
Thank you very much. Step 2 worked for me.
Thank you very much. Step 2 worked for me.
step 2 worked for me too (well after I double clicked in the cell after changing format to General) Thanks
Step 2 worked for me as well !
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