16

We have an existing spreadsheet that has been working fine. Today we double-clicked a cell, to edit the formula, but did not change anything (we tested on other cells to check). When hitting enter, or clicking another cell after getting the cell in edit-mode, the cell stopped showing the formula result and instead showed the formula.

e.g cell was showing "102". Double-click and it showed =100+2. Hit enter and it shows "=100+2".

We haven't changed workbook settings, so why would it start doing this and how can we fix it?

  • Try the following: click Format, Cell and select the Number tab. If the type is Text, change it to Number. – LDC3 Sep 13 '14 at 13:54
  • I will but it doesn't answer the question of why it suddenly went wrong :) – Mr. Boy Sep 13 '14 at 13:55
  • Most likely you got a space or some other character inserted ahead of the = sign in the field. It's easy to happen. – Tyson Sep 13 '14 at 13:59
  • 1
    Read the question... ALL I am doing is double clicking a cell and hitting enter. I did this on like 10 cells and the same thing is happening... I'm NOT modifying the formula. – Mr. Boy Sep 13 '14 at 14:09
  • 1
    If you have @ in your custom number format, Excel will sometimes incorrectly show the formula rather than the value – Andy Terra Nov 30 '16 at 21:57
17

You have "Text"-type formatting chosen.

I believe this has a tendency to "leak over" if adjacent cells have it.
It is an automatic formatting option, but please don't ask me how to turn it off.

Select the cell you have trouble with, then press CTRL+1 (Menu: Format > Cells)
Select "General" (in Excel) or "Category: All, Format: General" (LibreOffice)
... in the dialog that appears.

NOTE: You need to re-edit the cell too... (select the cell, hit F2 and then hit ENTER)

To reset ALL CELLS click the rectangle upper left - above [ 1 ] and left of [ A ] - then reset it the same way as above, the last step is a bit cumbersome :-I .

13

I think it's just that "Show formulas" is checked in the auditing pane.
Press CTRL+` and it should revert back

  • 1
    For German Excel it's CTRL+# instead – nixda Mar 15 '16 at 10:29
  • Wow, what on earth is that toggle even for? Seems like "ruin my spreadsheet with no undo" – Flash Apr 15 '18 at 19:27
  • 1
    What is the symbol next to CTRL? What other symbol can be found on the same key? – Nick_F May 30 '18 at 1:38
  • 1
    @Nick_F On a QWERTY keyboard it's with the tilde (~) to the left of the 1 key. Here's a full answer: superuser.com/questions/254076/… – Andrew Keeton Jan 3 at 19:14
1

This happened to me, but it seems to have something to do with opening an excel 97-2004 (xls) in excel for mac 2011. Once I "saved as" an xls, the formulas were able to calculate.

1

This happened to me with Office 2010. Inexplicably, I had the option set to show formulas instead of results for that worksheet. I changed that back. That had no effect. I then had to format the cell and change it from text to general (or number would probably work as I was able to change it to that afterward), then delete what was in the cell, then reenter (I hit the autosum as that happened to be what I was trying to do).

1

The way how to reproduce this problem in Excel 2013:

  1. Enter the formula in an empty cell, for example =D2
  2. Change the cell format to Text
  3. Press F2 + Enter

There are several possible workarounds I use for this situation:

  1. First change the cell format from Text to General, then edit the formula, then switch back to Text
  2. Delete the cell content (using Del key) and rewrite the formula text, change formatting to Text.
  3. Write the formula text to another cell and copy the whole cell

There is a thread, that deals with the problem:

If a cell is formatted as text when you enter a formula, and if you then change the format to (for example) General, you'll still see the formula. You have to press F2 then Enter, or enter the formula anew, to see the result.

0

The same thing happened to me. There is no Office button in Office 10, so to fix it, I did File > Options > Advanced > Display Options, and I unchecked the box that says Show formulas in cells instead of calculated results.

As for WHY it goofed up, I think someone accidentally typed a keyboard shortcut for this option by mistake, like CTRL + something (whatever the keyboard shortcut is to display formulas instead of calculated data).

Hope that helps.

0

This happened to me. I didn't change any settings. I fixed it as follows:

  1. I deleted all the areas that were not cooperating.
  2. I saved, shut Excel down and restarted.
-1

Nesting the formula in the VALUE function worked for me.
I.e., if my formula was =A1+A2, I would simply change it to =VALUE(A1+A2).

protected by Community Sep 18 '16 at 20:33

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.