MS Excel - Numbers with >15 digits and Scientific Notation
The Scientific format displays a number in exponential notation, replacing part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power.
A number format does not affect the actual cell value that Microsoft Excel uses to perform calculations. The actual value is displayed in the formula bar. (READ NEXT LINE!)
The maximum limit for number precision is 15 digits, so the actual value that is displayed in the formula bar may change for large numbers (more than 15 digits).
To reset the number format, click General in the Category list. Cells that are formatted with the General format have no specific number format. However, the General format does use exponential notation for large numbers (12 or more digits). To remove the exponential notation from large numbers, you can apply a different number format, such as Number.
Excel can hold numbers to only 15 significant figures because it uses IEEE Floating Point Maths, which dictates how numbers – which can be as large as 1.79769313486231E+308 or as small as 2.229E-308 – are stored without making the workbook consume gigabytes of space and take hours to recalculate. This limitation is clearly set out in the Excel help text.
Note that “significant figures” aren’t the same as “decimal places”; it doesn’t matter what size the number is or where the decimal point is placed, Excel will store only its first 15 significant digits and discard all the rest.
Excel Advanced Topics - Part 1
Crunched for time but want to learn the formatting shortcuts to a better looking spreadsheet? Click the link below for more info and to register for this workshop.