General discussion


Excel prob - formulas do not show result

By DiggerJackson ·
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.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by asdfg In reply to Excel prob - formulas do ...

Check that the column or values that you are trying to sum do not have a green comment arrow telling you that a number is currently formatted as text. If it has you need to change the cell format to be number. I couldn't get excel to change the format on some cells until I used a program called ASAP utilities (free download) addin for excel. You can select the cells / column / workbook and get ASAP to change all the cells with numbers to number format. This will fix your formulae.

Collapse -

by pten2106 In reply to

This problem does indeed seem to be caused / part caused, by inserting columns. At least it was after this that I noticed the errors in my worksheet.
Also, the errors seem to come from the source data & not the cells containing the Formula or Formula's themselves.
The only sure-fire way of fixing the problem, that I have found, is to Copy the source data only & Paste it into Microsoft Notepad. Then Copy & Paste the data back into Excel into exactly the same cells as it came from.
This fixed the problem for me.

Collapse -

Another solution

by ron.croweaz In reply to Excel prob - formulas do ...

As stated elsewhere, and it worked for me (the ctrl-tilde
specifically) (I don't know why it requires this, of course):

Displaying Cell Formulas Rather Than Results
There are two ways to change Excel's cell display for
formulas to display the actual formula rather than the
formula results.
Keyboard shortcut/toggle -- Ctrl-~ (Ctrl-Tilde)

Tools | Options | View tab | Window Options section,
check Formulas

Collapse -

Remove space

by rkpdm In reply to Excel prob - formulas do ...

Looks like there is space before and after your number. Select the cell, go to formula bar and remove any space before and after the number. This should work.

Collapse -

'Text' format is sticky

by trev.k In reply to Excel prob - formulas do ...

This has never been a problem to me before, but I realised that I'd told the sheet at the start to have the format as 'text' (to get around excel changing date formats stupidly). Changing back didn't actually change it back, because the text format is stored. Changing to 'general' format got rid of just the formulas not the results, but the results were wrong. There is still a problem if there is a little green arrow in the cells. This was also buggering up my calculations for, e.g. averages, treating numbers as text and ignoring them. Even though I'd changed the format from 'text' to 'general', it was still saved or 'stored' as text. The way I fixed it was to click on a cell, then click on the option that came up, selecting 'convert text to number' that dropped down next to it. Or just double clicking on the cell.

Collapse -

See formula results by changing default cell format settings

by ematus In reply to Excel prob - formulas do ...

My Default cell format setting on Excel has been "Text" which prevents formulas from being recognized as formulas. When I change it to "General" my formulas work and the results are displayed.

Collapse -

I think this is the definitive answer. For me at least....

by BigDixter In reply to Excel prob - formulas do ...

If you type a formula in a cell that is set to text AT THE TIME OF TYPING then the formula will be treated as a "constant", not a formula. This rule applies even if you then change the format to general.

If you change the format to General and then edit the formula it will work.

I find that the issue arises in Excel 2010 and earlier versions. It generally happens when I download a spreadsheet from a reporting tool or a website report where the process of downloading appears to make a "best guess" as to the required format for a column. The problem then arises if you are inserting columns and they inherit the format of the column to the right.

Easiest cure is to highlight any cells that you are adding formulas to and make then "General" before you start adding formulas.

I have just spent a couple of hours pushing this about but have been mega frustrated for years with this so now have the smug "Gotcha" feeling. Thanks to all the previous posts for some key insights.

To avoid it I just highlight every cell and change the format

Collapse -

There's one more quirk

by kcskrobela In reply to Excel prob - formulas do ...

Big Dixter's answer totally works but there's one more thing you need to be sure of. There's a way you can toggle between viewing formulas and viewing results, by doing
Ctrl-` (that is, Ctrl + grave accent normally next to the number 1). If you've been trying without success to show your results by using this toggle, try doing it one more time. If you've formatted the cell as "General" and typed a formula, the results should now show.

Whoa! And thanks to everyone else for solving this for me!!!

Related Discussions

Related Forums