MS Excel showing the formula in a cell instead of the resulting value

19
votes

excel sheet showing the 3 cells with formula instead of value

The above image shows 3 cells containing the formula instead of the value; The first cell is under PHP Hourly Rate, the second is under Name and the third is the Invoice Sub Total. However, the values will show after I double click the cell then press enter. Why is this happening? I am using Excel VBA on Excel 2013.

Thanks.

8
excel
check the format of the cell, make sure it's not formatted as text but General. This question is not about programming defined in the help-centre nor it's about programming Excel therefore voting to close. - user2140173
Is your cell formatting set as text? If so, I believe that will make it ignore formulas - bmgh1985
Excel options -> Advanced -> show formulas in cells instead of their calculated results. But yes, this is off-topic and should be closed. - DavidG
I'm already changing the format of the column from text to general using these: Columns("B").Select Selection.NumberFormat = "General" but I noticed that it is in the middle of the code so I put it on top before everything else then it worked. Thanks. - clintgh
I've edited your question as there is no example of code, simply stating "I'm using VBA" but not referencing it anywhere, not even in the problem, means the tag is not needed. Your statement about using Columns("B").SelectSelection.NumberFormat = "General" is not mentioned in your question at all! To any VBA user, this question looks like you've formated a cell incorrectly, which you have, but you didn't tell us you'd done it in VBA. Please post your code if it's a VBA question!!! - SilverShotBee

8 Answers

42
votes

Make sure that...

  • There's an = sign before the formula
  • There's no white space before the = sign
  • There are no quotes around the formula (must be =A1, instead of "=A1")
  • You're not in formula view (hit Ctrl + ` to switch between modes)
  • The cell format is set to General instead of Text
  • If simply changing the format doesn't work, hit F2, Enter
  • Undoing actions (CTRL+Z) back until the value shows again and then simply redoing all those actions with CTRL-Y also worked for some users
5
votes

If you are using Excel 2013 Than do the following File > Option > Advanced > Under Display options for this worksheet: uncheck the checkbox before Show formulas in cells instead of their calculated results This should resolve the issue.

3
votes

If all else fails, Ctrl-H (search and replace) with "=" in both boxes (in other words, search on = and replace it with the same =). Seems to do the trick.

1
votes

I had the same problem and solved with below:

Range("A").Formula = Trim(CStr("the formula"))

0
votes

If you are using VBA to enter formulas, it is possible to accidentally enter them incompletely:

Sub AlmostAFormula()
    With Range("A1")
        .Clear
        .NumberFormat = "@"
        .Value = "=B1+C1"
        .NumberFormat = "General"
    End With
End Sub

A1 will appear to have a formula, but it is only text until you double-click the cell and touch Enter .

Make sure you have no bugs like this in your code.

0
votes

Try this if the above solution aren't working, worked for me

Cut the whole contents in the worksheet using "Ctrl + A" followed by "Ctrl + X" and paste it to a new sheet. Your reference to formulas will remain intact when you cut paste.

0
votes

Check for spaces in your formula before the "=". example' =A1' instean '=A1'

0
votes

I tried everything I could find but nothing worked. Then I highlighted the formula column and right-clicked and selected 'clear contents'. That worked! Now I see the results, not the formula.