Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells show formula rather then result (2002)

    I have started to get a problem since using 2002 (never before) where, especially after editing a cell, Excel insists on displaying the formula in the cell, rather than the result. I can change the formula completely but still only the formula displays, even after manual calculation. Other cells calculate normally but, once in this 'mode', the same thing happens to other cells on the sheet that are edited. Sometimes, the only solution is to copy the whole sheet to a new one and delete the original.

    Has anyone else come across this behaviour and found a solution?

    Mike

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather then result (2002)

    I'm working on 97, but can we assume you've unchecked tools/options/view/window options/formulas?


    Brooke

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    Thanks, Brooke

    No, as I said, it just affects some cells. One minute they're showing the results normally, the next (after editing) they refuse to show anything except the formula.

    It's almost as though the formula has been converted to text. I thought at first I may have mis-typed something (for example removed the equals sign by mistake), but I can clear the cell contents and type in a completely new formula (e.g. =2*2) and still the formula displays.

    Mike

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cells show formula rather than result (2002)

    Hi,
    Is there any chance that the cells have been formatted as text after the formulae were originally entered? If you enter say =2*2 in a cell, it will display 4. If you then format the cell as text it will still display 4, but if you then select the cell, press f2 to edit and press enter, it will display =2*2 rather than calculating it.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    Hello Rory, thanks for the response.

    I'm pretty sure I haven't re-formatted the cells on those occasions (formatting as text is something I do fairly rarely anyway), but that's certainly the effect.

    I'll double-check it next time it happens.

    Thanks again,

    Mike

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    Mike, select all cells that are exhibiting this behavior. Hit Edit-Clear-All. Save the file (a must for the "clear all" to take effect). Now enter your formulas.

  7. #7
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    Thanks - I guess nukeing them should do it.

    Any idea what might be causing it?

    Mike

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cells show formula rather than result (2002)

    One thought is to check the style being used (Format, Style) and see if its number format default is text. Whenever you insert a new cell, column or row, the new cells will be the normal style setting default. BTW, it always helps to post a small example worksheet so we can beat up on it to see what the reason is.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    Thanks for the suggestions, John.

    I don't normally use styles in Excel, so I don't think that would cause the change in the behaviour of the cells, but I'm keeping all these suggestions and, when it happens again, I'll work through them.

    Good idea about the example worksheet, by the way but, on this one, I've always sorted them out one way or another and moved on, and I can't create the problem at will.

    Thanks again.

    Mike

  10. #10
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells show formula rather than result (2002)

    I had another look at the Knowledgebase, and I think I found the problem.

    As several of you splendid people suggested, it's caused by the cell being formatted as text, but not as a result of anything that's been done actively. I quote from KB article Q213970 :-

    "When you link cells in Microsoft Excel, the format of the original cell is
    applied to the linked cell. Thus, if you enter a formula that links one cell to
    a cell formatted as text, the cell with the formula is also formatted as text."

    The tricky part is that, when you first create the formula, the result displays normally but, as Rory suggested, if you then edit it, the cell just displays the new formula.

    Even trickier, if you then remove the text formatting, the formula still continues to be displayed until you edit the cell again, then everything returns to normal. I've been able to reproduce this.

    Strangest of all is that this behaviour appears to have been the same since Excel 5.0 yet, though I've been a fairly heavy user of Excel since version 2, I've never come across it before the last couple of months.

    Many thanks once again to everyone who helped with this.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •