18

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?

Improve this question
5
  • Try the following: click Format, Cell and select the Number tab. If the type is Text, change it to Number.
    – LDC3
    Sep 13, 2014 at 13:54
  • I will but it doesn't answer the question of why it suddenly went wrong :)
    – Mr. Boy
    Sep 13, 2014 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, 2014 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, 2014 at 14:09
  • 1
    If you have @ in your custom number format, Excel will sometimes incorrectly show the formula rather than the value
    – airstrike
    Nov 30, 2016 at 21:57

8 Answers 8

Reset to default
21

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 .

Improve this answer
16

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

Improve this answer
4
  • 1
    For German Excel it's CTRL+# instead
    – nixda
    Mar 15, 2016 at 10:29
  • Wow, what on earth is that toggle even for? Seems like "ruin my spreadsheet with no undo"
    – Flash
    Apr 15, 2018 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, 2018 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, 2019 at 19:14
2

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.

Improve this answer
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.

Improve this answer
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).

Improve this answer
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.

Improve this answer
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.
Improve this answer
0
-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).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .