51

When I open a plain new Excel sheet (2016), change the type of the cell to Number and enter the number 1.25, it will change the entered number to the value 43125,00. I get similar results when I enter 1.01 or 1.9, but not if I enter 1.99 – that is treated as text or at least something unparsable as a number.

I understand that I have to use , to correctly input numbers.

My question is, what happens when I enter 1.25 as a number? Why do I get 43125,00?

  • 1
    Note that "cell type" isn't the type of the data - it only affects how the data is displayed, not how the input is interpreted. All data in Excel is in variants. – Luaan Jun 2 '18 at 14:00
108

For your locale settings, , is the decimal separator, so that's what you need to use to enter a number. If you use a period, the input will be interpreted as something other than a number; what that is depends on the content.

If what you enter could be a valid date, a period is being interpreted as a date separator. Excel thinks 1.25 is a date (January 25, 2018), which is represented internally as 43125 (the number of days since January 0, 1900).

Since you formatted the cell as a number rather than a date, you're seeing the numerical representation of the date. The default decimal places is set to two, giving you the zeros at the end. It's pure coincidence that the "125" pattern is repeated in the day count representing that date.

Both 1.01 and 1.9 could be dates, so the same thing happens to them. But 1.99 cannot be a date, so the literal value, as entered, is stored as text.

  • 5
    This is annoying in excel, I work around by adding a single speech mark before the number, so "1.25" becomes " '1.25" and fortunately excel treats it as a number. – Kevin Anthony Oppegaard Rose May 30 '18 at 7:50
  • 19
    @KevinAnthonyOppegaardRose: in this question decimal separator is ,, if you want to enter numbers you need to use the decimal separator. If you use other characters Excel will interpret it another way. – Máté Juhász May 30 '18 at 7:54
  • 21
    @KevinAnthonyOppegaardRose The speech mark ' is the way to make Excel take anything as text, so it really is not treating it as a number but as text, and after a ' you can write anything and Excel will not try to interpret or convert it – fernando.reyes May 30 '18 at 15:38
  • 24
    and it's called the apostrophe btw – phuclv May 30 '18 at 17:22
  • 2
    @EricDuminil So you think it's stupid that Excel guesses at types, and you want to solve it by having Excel make even more guesses? What could possibly go wrong :) It's certainly weird that it doesn't preserve one type even in a table column, but the truth is - have you seen how people use Excel? In the end, "it behaves the same way in all contexts" is at least consistent. And the OP's problem is 100% about them not using their locale's number format - are you really trying to say Excel shouldn't expect people to use their local number (etc.) formats? – Luaan Jun 2 '18 at 13:58
3

After reading the comments, I realized now that the mechanism behind the problem is more complicated than I thought. First, the decimal separator is a comma and not a full stop. Second, Excel classifies the input value as a 'date' value but represents it using the 'number' format.

One solution to the posted problem is to change the decimal separator, and it can be done from Excel, Options -> Advanced -> Editing options -> Decimal separator. If this is fixed, Excel would not have classified the input value as a 'date' value.

The decimal separator is not something I would immediately consider because, who would use a comma as the decimal separator? And yet, interestingly and surprisingly, half the world actually uses it (https://en.wikipedia.org/wiki/Decimal_separator).

However, I did recognize the 43125 as a 'date' value in numerical form. If we take away the issue about the decimal separator, a similar example would be 1-25, which Excel would interpret as a 'date' value and under the 'number' format gives 45658.00. More confusingly is that Excel allows custom formats so that the 'date' value can appear as 1.25 or 1,25 or any other form.

I used a lot of 'date' values in spreadsheets for calculations and they can be annoying. Occasionally when I reopen these spreadsheets, Excel (or Openoffice) would think they know better and automatically convert these date columns from the 'date' format to the 'number' format for no reasons. In these situations, right-click mouse -> Format Cells -> Number and reset to the correct format for the cells/columns would recover the data.

  • No. This is an automated process in Excel. See the answer from fixer1234 – Kevin Anthony Oppegaard Rose May 30 '18 at 13:38
  • 1
    Excel thinks the input is a 'date' and automatically changes the number format to 'date'. What I supplied here is a way to 'tell' Excel the input is a 'number' and fix the format to the correct one. – wtong May 30 '18 at 14:29
  • 1
    I don’t agree that the first paragraph is missing what’s happening.  It does seem to be a complaint, along the lines of Eric Duminil’s comment.  Most of us will agree that, when you type something into a blank cell formatted as “General”, Excel should try its best to figure out what your input means.  But there’s a school of thought that, when you type something into a blank cell formatted as “Number”, Excel should try its best to interpret that input as a number.   … (Cont’d) – Scott Jun 1 '18 at 20:09
  • (Cont’d) …  One might argue that changing the implementation now would break things.  On the other hand, the current implementation is not clearly consistent.  For example, in my English (United States) system, if I type 10-2 or 10/2 into a cell formatted as “General”, I get “2-Oct”.  Do the same thing with a cell formatted as “Number”, and the 10-2 gives me “43375.00” (numeric value of “2-Oct-2018”), but the 10/2 gives me “5.00”.  … (Cont’d) – Scott Jun 1 '18 at 20:09
  • 1
    @JKreft I think Excel does the whole thing only if you pick a specific category and then insert an invalid value. So Excel only bites you in the ass if you first make a mistake. Guessing what the user meant from invalid input is simply very difficult. – Voo Jun 2 '18 at 8:34

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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