Formula showing in the cell but not the result.

[Majen]

I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.

The same formula could be another place in my worksheet and work fine.

I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.

Help!

Thank you.

 

[perco754]

Noop sir, this is not working for me sorry

General method for fixing the following problem:

"All my formulas have ended up in text formatted fields & are held as text values. Even changing the format doesn't seem to help".

1) Go to - Edit | Go to | Special | Formulas. Press OK
2)Go to - Edit - Replace...
3) Find: =
4) Replace with : =
5) Click Replace all

In most cases, has the 'effect' of re-entering the formula(s), & getting Excel to recognise it as a formula not a text string.

Paddy

 

[barredoteam]

Like 10 years late, but in excel 2010 it's super easy.
Just Hit "alt" then "M" THEN "H".

That solve my problem.
You can do it back and forth.

 

[Scramble!]

The solutions offered above do not always work with XML files that were imported from other applications. I have encountered files such as those that, when opened in Excel (2003+ versions), will not display the results of a formula. Even one as simple as =left(B13,12).

In this case, when you open the formula in the formula dialog box, the dialog boxes' preview window correctly displays the result. Click ok, and the cell itself still displays the formula. Editing it with F2 does not help, nor does changing the cell format to Text, General, or any other format.

However, if you copy all of the data from the worksheet (click in cell A1, then CTRL+SHIFT+END, then CTRL+C) and paste it into a new, clean worksheet (CTRL+V), the results of the formula are correctly displayed. If necessary, you can then use the CTRL+` (that's the accent mark below the tilde ~ key, not the apostrophe or single quote key) keyboard shortcut to toggle between displaying and hiding cell formulas.

I believe this highlights a bug in Excel's interpretation of the XML header information in the exported/imported file. I don't have time to troubleshoot it further, but hope this will help in some cases like I have run across.

 

[XOHelo]

barredoteam, i found it easier in Excel 2010 too.

I kept trying to get the formula to stop displaying and start showing the result. Every time I retyped the formula, and go back to view the cell in the formula bar, I saw excel kept populating a ' before the =. So, I went up to the format drop-down and saw that it was on text. I changed it to general and the problem was solved.

 

[Scramble!]

@XOHelo: You are absolutely correct! Obviously, when pasting into a NEW (Excel) spreadsheet (my solution), all of the cells are pre-formatted as General, while the cells are pre-formatted as Text when the data is imported into Excel in XML format. We have found that if you FIRST format the cells from Text to General (in an XML spreadsheet) where you will be placing formulas (your better solution) BEFORE entering the formulas, the results display properly.

However, if you enter the formulas while the cells are still in Text format, then later changing the cells to General format will NOT make the formula result show...the cells will still only display the formula. We've found no way to later "fix" those cells to properly show the results if the formulas were entered into cells that were still Text format. The cells must be formatted General BEFORE entering the formulas as far as we can tell.

 

[GSROBINSON]

Ok - new to the forum - LONG time fan of MrExcel.

I had this problem using the "=concatenate" function in MS Excel 2007.

Struggled with changing field types --- no luck.

Easy fix - I simply went to the fx line and typed a space (" ") and hit return - the results of the formula popped up immediately. Then handle drag and copy across the entire column yielded the expected results.

The formula that I was working with was built using the fx build screen and selecting cells alternating with text input "-"

=CONCATENATE(Table_owssvr_1[[#This Row],[Program]],"-",Table_owssvr_1[[#This Row],[System Version]],"-",Table_owssvr_1[[#This Row],[Event]],"-",Table_owssvr_1[[#This Row],[Log Number]])

Hope this helps some other poor soul.

-Greg

 

[Dewdrop1212]

Hi, I have Office for Mac with Excel 2008 and my formulas wouldn't show the results either. Nor could I get the column formatting to "stick".

The following tip posted above solved both problems:
=========================================
I know this is an old post but here is what works in Excel 2007. From the Microsoft help files.
Switch between displaying formulas and their values on a worksheet

• Press CTRL + ` (grave accent)

==========================================

Thank you!

 

[Nuruddin]

hi, I want to have text in formula bar instead of formula, if a1 is Cucumber and value is 1000 and a2 is koosa and the value is 500, the balance is want in formula bar is Cucumber - koosa and the result will come in a3 as 500. Hope u understand my message, Regards Nuruddin Sayyed

 

[ksb1972]

Noop sir, this is not working for me sorry

Another reason I've seen this happen is when the workbook has seen corruption. Google "too many cell formats" for an explanation.
For an easy visual indicator of internal corruption caused by this problem, check the cell styles drop list. If it's full of strange characters instead of the defined styles, you know the workbook has been corrupted.

The easiest/best solution in this case in my opinion is to re-create the workbook as I'm not aware of any tools that can repair the invisible damage that leads to this unpredictable/undesirable behaviour.

 

[MatthewLane412]

I had a similar issue. I found that deleting the apostrophe before the equal sign fixed it