In some problem workbooks, formulas do not display their results. My question is how do I display the formula result instead of the formula itself? I am using Excel 2003.
I am having this problem with SUM and CONCATENATE -- I can't get the result to display, only the formula itself. I have run into this problem in several workbooks before, but I never have been able to rectify it. It is also difficult to reproduce the problem. I have kept the worksheets that have these problems and can send them to anyone willing to look. My only workaround is to open a new workbook and re-enter the data. I can even copy the data from the problem workbook and paste it unchanged into a new workbook and the formulas display their results as expected.
Here are some more details:
With SUM, I have set the format to number for both the data and the formula, but this does not fix the problem. Additionally, I have made sure that I do not have "numbers stored as text" by using the paste special "add 0" or "multiply by 1" workarounds. When I use Formula Auditing | Evaluate Formula, it shows me the correct formula and result. When I exit out of formula Auditing, only the formula is displayed in my workbook.
I can hit [CTRL+`] and that changes the formula to show the value. But it has the unfortunate side-effect of messing up the formatting of the entire workbook.
What is going on here?
One observation: when I type in numbers in these problem worksheets, they stay as number category "General" but they become left-justified in the cell. The strange thing is that I cannot change the justification -- it stays left-justified no matter what I do. Normally, when you enter a number into a "General" category cell, it automatically becomes right-justified. I can set the cells to category type "Number" and when I enter data (either numbers or text), it still is fixed left-justified. This does not happen in non-problem workbooks.
Help!
This conversation is currently closed to new comments.
I found success with a very simple but dumb step. If the names of functions in your formula are lowercase, try manually capitalizing them. This seems to "trick" the system into showing the formula result instead of the formula itself. It worked once for me, but I didnt test it extensively to see if it always works.
Here's an example: Change "=if(isblank(A1)=true," ","It's Blank!") to "=IF(ISBLANK(A1)=TRUE," ","It's Blank!")
What a farce. Was reading this forum because of this problem (obviously!) and yes using capitals to retype the formula works perfectly, weird, because when typing it excel gives you the 'hints' but then displays as text!
I realize it's been months since the original message, but I ran into this twice recently and I haven't seen this solution written down. For me, Excel displays the formula, not the result, occasionally in COLUMNS THAT I HAVE INSERTED. When I looked at the format for that column (select column, Format, Cells..., Number tab), no format was selected. Set the format to General, click OK, then delete and retype the formula and it should now display the result.
Like a dummy I was changing the formating of the cells with the data, not the cells with the actual FORMULA. Once I turned those cells into GENERAL formating, everything worked like a charm. Thank you.
I think that everyone is correct on the issue here. The issue I believe is with Excel thinking it is a text string. After you change the format to general, go to the formula and just add parantheses around a term within the cell and hit return. This works for me.
Hi All, I have just experiance this problem on my wifes laptop and I thought I was going mad. I have tried most (if not all) of the suggestions that have been posted, and NOTHING worked everytime as a fix. Sometimes re-formatting the cell did the trick, sometimes capitalizing the formula worked. I have even retyped the formula in a new worksheet that I added to the file, and this only had about 25% success. If anyone has any sure fire methods to fix this issue, please let me know.
What about removing all of the formatting, then applying the General formatting. Just a thought. All of this apply this or that formatting could be confusing it's little Excel brain.
I have been using Excel 2003 for a few months and haven't seen it yet.
The only way I can get this problem is if I format a cell as text then enter the formula. Then the cell displays the formula. As soon as I changed the format to general the result displayed.
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Excel prob - formulas do not show result
I am having this problem with SUM and CONCATENATE -- I can't get the result to display, only the formula itself. I have run into this problem in several workbooks before, but I never have been able to rectify it. It is also difficult to reproduce the problem. I have kept the worksheets that have these problems and can send them to anyone willing to look. My only workaround is to open a new workbook and re-enter the data. I can even copy the data from the problem workbook and paste it unchanged into a new workbook and the formulas display their results as expected.
Here are some more details:
With SUM, I have set the format to number for both the data and the formula, but this does not fix the problem. Additionally, I have made sure that I do not have "numbers stored as text" by using the paste special "add 0" or "multiply by 1" workarounds. When I use Formula Auditing | Evaluate Formula, it shows me the correct formula and result. When I exit out of formula Auditing, only the formula is displayed in my workbook.
I can hit [CTRL+`] and that changes the formula to show the value. But it has the unfortunate side-effect of messing up the formatting of the entire workbook.
What is going on here?
One observation: when I type in numbers in these problem worksheets, they stay as number category "General" but they become left-justified in the cell. The strange thing is that I cannot change the justification -- it stays left-justified no matter what I do. Normally, when you enter a number into a "General" category cell, it automatically becomes right-justified. I can set the cells to category type "Number" and when I enter data (either numbers or text), it still is fixed left-justified. This does not happen in non-problem workbooks.
Help!