5

I've used Excel more than I care to think about, but this is a new one for me. I have a cell with a simple formula, =Counts!E3. I set the number format to General. I edit the cell (just F2-Enter, no change). The formula no longer works, and when I check the number format, it's now Text.

Has anyone else seen this behavior? Maybe found a fix? I can change the format to General and hope no one touches it (or set up worksheet protection), but if there's a hotfix or something that would be groovytacular.

2 Answers2

3

You need to set the format to Number.

General means Excel is going to guess, and thus change the format on its own. This is usually more apparent when you enter in Dates/Time. Best that you go ahead and explicitly change it if this happens again.

surfasb
  • 22,632
  • Very strange. I realize that General allows Excel to use a variety of formats based on its guess as to what the data is, but I've not seen it actually change the cell's type before. Also odd, changing the type from General to a custom type was not good enough: it still changed it to Text. But setting it to Number worked. Fortunately, I'm not using this cell to display anything, so I don't actually care what format Excel uses... this time. –  Jan 25 '12 at 22:18
  • I see this all the time when a CSV is imported. By default, it is imported as General. – surfasb Jan 26 '12 at 01:21
1

I had same / similar problem with excel. Even if I changed the format of the cell to either General or Number. If I entered the formula after formatting the cell everything was fine, however, if I made a change to the formula the cell format automatically changed to Text. On closer examination I discovered that other cells referenced by the formula even though were displaying numbers were actually formatted as text, therefore, excel changed the cell containing the formula to text.

Phil
  • 11
  • 1
  • It happens when the formula uses a source cell that is formatted as Text. Excel gets the formula right the first time, but then changes your cell's format to Text (so the formula is shown as plain text) if you edit it. This is illogical - Excel knows you're using a formula, but breaks it! The only solution, to keep your cell as General or Number (i.e. to keep your formula working even if you edit it), is to make sure that your source cells are not formatted as Text. Number format is safest - General should be OK, but it gives Excel more opportunity to get things wrong. – Laurence Renshaw Jul 17 '19 at 02:51
  • @LaurenceRenshaw- Thank you. This should be the answer – Dave Pile Oct 07 '21 at 21:34