19

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.

LBPLC
  • 1,570
  • 3
  • 27
  • 51
clintgh
  • 2,039
  • 3
  • 28
  • 44
  • 3
    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](http://stackoverflow.com/tags/excel/info) therefore voting to close. –  Jun 20 '14 at 09:20
  • Is your cell formatting set as text? If so, I believe that will make it ignore formulas – bmgh1985 Jun 20 '14 at 09:20
  • 1
    Excel options -> Advanced -> show formulas in cells instead of their calculated results. But yes, this is off-topic and should be closed. – DavidG Jun 20 '14 at 09:30
  • 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 Jun 20 '14 at 09:31
  • What is wrong with my question? This is programming, I am programming Excel using VBA. Where should I post this question, then? – clintgh Jun 20 '14 at 09:32
  • 1
    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!!! – LBPLC Jun 20 '14 at 09:39
  • @SilverShotBee whoa, chill. Easy with the exclamation points. I'll do it next time. At least the problem was solved. – clintgh Jun 20 '14 at 09:45

8 Answers8

42

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
Community
  • 1
  • 1
bubbassauro
  • 3,969
  • 2
  • 45
  • 45
5

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.

shezi
  • 61
  • 1
  • 5
3

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.

MO'R
  • 41
  • 1
1

I had the same problem and solved with below:

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

S Kodama
  • 101
  • 1
  • 4
0

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

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.

Neeraj Sharma
  • 1
0

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

Edisson Gomez-Baquero
  • 1
0

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.

LaVonia
  • 1