So that they are displayed incorrectly in the 2E + 09 graph, but normally in the decimal system without abbreviations

Closed due to the fact that it is off-topic by MSDN.WhiteKnight , 0xdb , user192664, nörbörnën , mymedia October 18, 18 at 18:43 .

  • Most likely, this question does not correspond to the subject of Stack Overflow in Russian, according to the rules described in the certificate .
If the question can be reformulated according to the rules set out in the certificate , edit it .

  • one
    um .... set "cell format"? - Fat-Zer
  • Usually Excel automatically switches to the scientific format, if the usual lack of space in the field ... try to push the boundaries of the display value. - Akina
  • It cannot show the exact number as the number is too large for the computer. - ishidex2
  • @Akina If the number is not in the cell, then ##### is displayed, not the scientific format. - Enikeyschik
  • one
    This question should be closed, because it does not correspond to the subject of the resource - nörbörnën

1 answer 1

The first thing to consider is that the format of the value and the format of the cell are not the same thing.

What we see ( 2E + 09 ) is the exponential form for recording the number 2,000,000,000 (not only this, see below). The same number can be obtained:

=2*10^9 =2*1e9

The number is not truncated, but displayed in a different form. The value in its normal form can be viewed in the formula bar.

But here's bad luck ... 2100000000 in exponential form will have the same record - 2E + 09 . And all because the cell format is set to Exponential , and even with zero number of characters in the fractional part. Put two decimal places - we get 2.10E + 09

Excel compares the value with the specified formats and determines how to display it. Sometimes "evil joking." For example, a record 1.02 (not in a text cell) is taken as the date of the day.month. (Current_year) , 11.22 - as the date of the month.day. (Current_year) . In this case, Excel changes not only the display format - it will also change the value! Instead of the entered 1.02, we get the date 01.02.2018 . Or, if we set the cell format to General , we will see 43132 (the number of days after 01/01/1900 )

The cell format could automatically change to exponential in the case where the cell width was insufficient to display the full number. Could pull up when copying data.

To fix the "cropped" is simple: set a sufficient width of the cell, set the format to Common or Numeric with the necessary number of decimal places.

Important note. Large numbers are still cut off, but this is not related to the exponential notation.

Excel has a limitation when working with numeric data - no more than 15 digits. If the number 12345678901234567890 is entered in the cell, we get the value with zeroed low-order digits 12345678901234500000 , from 1.2345678901234567890 only 1.23456789012345 remain.

You can avoid clipping by setting the cell text format. The text can be stored and a long number. It is also necessary: ​​bank accounts, stock numbers ... Ie Fatically not numbers, but a text consisting of numbers. But to use such values ​​as numbers will not work - at the first attempt, the lower digits will be reset.

For the sake of justice, it should be noted that it is possible to work with very large numbers, the width of which exceeds 15: to store values ​​as text, when used, divide into valid fragments ... But this is not easy and goes beyond the scope of the question.