Software

Ask something
Discussion Locked

Excel prob - formulas do not show result

0 helpful
38 replies

by diggerjackson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

38 replies

by zimonar

Select your worksheet and click Format from the Menu bar, click Cells?, activate the Number tab and select General from Category. Activate Alignment tab and select General from Horizontal drop down arrow, in Text alignment. Then select Bottom from Vertical drop down arrow, in Text alignment and click OK. Try again Ctrl+` if the formula result is not displayed.

by pmwork1

No this doeas not work, and beware if you have a range of different formats throughout, percentage etc selecting the worksheet changes ALL to that format.

Not a godd recommendation sorry

by diggerjackson

Thank you for your response Zimonar {Answer 1 from 05/24/2006}.

I selected the entire worksheet and set format cells as follows:
[category="Number", horizontal alignment="General", and vertical="Bottom"]

Nothing changed. All of my numbers stayed left-justified and the formulas displayed as entered, not with their results. CTRL+` does correct the problem (right-justify all of the numbers and display the formula results), but the rest of the workbook's formatting changes from the way I set it and it requires a great deal of work to reset it all.

As I mentioned in my post, I can copy the numbers and formulas in this problem worksheet and paste them into a new worksheet and everything works as expected (the problems no longer exist). However, when I do this, most of the formatting is lost and I have a lot of specially formatted text and numbers in the worksheet. Thus I cannot simply copy and paste a formatted worksheet (or a portion of one) into a new one. If I do, I then have to go back and re-format everything. If I copy the entire worksheet, the problem travels with it!

I also tried copying the numbers and SUM formula and then Paste Special ! Values. The result (the sum of the numbers) is then correctly displayed, but the numbers (and the sum) remain left-justified and the formula is no longer there, just the result. The category of everything remains numeric, but you cannot use any of these numbers in any formulas (not even =A1+B1 -- the addition formula is displayed, but not the result). If I do a CTRL+`, the formulas work but all of the formatting is lost.

Sometimes hitting F2 & Enter fixes these numbers not being treated as numbers problems, but that is not working in this problem worksheet.

So I am still searching for the answer.

by zimonar

Sorry, I forget to tell you to select also Context from Text direction drop down arrow under Right-to-left, in Format Cells dialog box. Select your worksheet, reactivate the Alignment tab and select Context.
- [category="Number", horizontal alignment="General", and vertical="Bottom"], leave it as it is and click OK.
- Try again Ctrl+` if the formula result is not displayed.
- I think your worksheet is already formatted incorrectly and saved. That?s why you are having problems.

by diggerjackson

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

by diggerjackson

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

by diggerjackson

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

by xodu27

I believe what will work, and has worked in my case, is a simple recalculation. Select sheet or problem areas, format as general cell catagory, press F9 to recalculate, then retype cell formula if needed. Hope this works for you.

by romdel94

Thanks a lot man. You can also go to Tools, Options, Calculation then click Calc_Now(F9).

by zimonar

And if not, try to retype your data(s) on another sheet in a new workbook, and not copy.

by curtis.hearn

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!")

Hope it works for you!

by sireynolds

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!

by cjohnsonmn

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.

by cecilia

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.

by hcmccombie

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.

by john_rivers

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.

by elongp

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.

by locolobo

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.

by lindaf

Go to tools, options, and the View tab. Uncheck Formulas under windows options. Then your cells will show the results, not the formulas.

by vicvergara

This is the only solution that worked for me, I tried all the others previously posted.
-Do you know why does this "Formulas" checkbox is checked even when the workbook is new and i never entered the options menu?

by acolbran

Restart XL2K3. Open your previous spreadsheet.
Copy the fields that you need. Create a blank worksheet, paste the records. Add new formulas as per requirement eg =A2&"."&B2
Worked for me. Nothing else did.

by mrchrstn

I also tried everything up to the previous post, and got nothing.

Only the last post solved my problem (though I'd been able to work through similar problems with the previously offered solutions).

I closed and reopened the file, and when I brought it back up, Excel told me I had a circular reference (which was true). I fixed the circ ref, and everything displayed fine.

Weird.

by jtackers

I have a similar problem with worksheets that are exported from software that controls an instrument in our lab (BioRad iQ5 Real-Time Thermocycler). None of the easy fixes seem to work (F9, formatting, etc.) A couple of times I have managed to fix the problem, but it is usually not clear exactly what I did. Sometimes if I hit ctrl + `, the formula (which won't evaluate) will outline the reference cells in color, other times it won't. Manually typing in caps has seemed to work, but not recently. The autocomplete (yellow box with formula syntax hint) function seems to recognize what I type as a formula, but still the formula won't evaluate. One time I looked at settings for "allow users to edit ranges", without changing anything, and that seemed to help; but I couldn't replicate it. What worked most recently was when the non-evaluating formula cell was active (i.e. I was editing the formula), I clicked the green check icon button in the formula bar and the formula evaluated.

I am not sure why this is happening, but the workbooks exported from the iQ5 software have some strange properties. First off they are protected (luckily no password), and have frozen panes and sometimes hidden columns. They also have shaded areas and colored fonts, which are very unusual properties for exported files from instrument software. The other thing that is unusual is the way in which they are exported; you just right click and a workbook (with multiple, populated sheets) opens up in Excel. Usually you save data tables to a csv format or something similar and then open it up. It's fancy and convenient until you try to edit the sheet. Obviously this formula problem is a hangover from the export function. The software has the look of Labview, but I am not certain that it was developed with that.

I was using RIGHT and LEFT functions to create new variable columns in Excel before importing into a statistical analysis program.

by yuncofoobar

In Excel2k3, goto Tools>Options>Calculation. Set the Radio button from Manual to Automatic.

Cheers.

by wolvers

The Control and tilda will change your formulae back and forth from text to values: Ctrl~

by wolvers

The Control and tilda will change your formulae back and forth from text to values: Ctrl~

by skreesha

I had the same issue. It seems that the SUM display worked when I made a single column of numbers SUM in a given new file. Then after I opened the file up and created a second SUM column, I had the display issue. I found that after I entered the formula for that second column and saved the file, when I opened the file up again the result (SUM) would display....go figure :)

by asdfg

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.

by pten2106

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.

by ron.croweaz

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

by rkpdm

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.

by trev.k

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.

by ematus

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.

by bigdixter

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

by kcskrobela

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!!!

This discussion is closed to new replies.