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!
