Excel maximum number of digits
In Excel spreadsheet, there is a limit for storing a number in a Cell, which is 15 digits (15 numbers) regardless of whether the numbers are decimal places. Excel call this “15 significant digits of precision” which adheres to “IEEE 754”. It is not difficult to understand why Excel limits the number of digits, because some numbers are infinite, such as π number (3.1415….), it is not possible to use infinite computer memory to store infinite numbers. We need to concern about this issue, because calculation would yield unexpected result.
The follow examples illustrate how Excel treats number more than 15 digits.
Case 1: Directly type a number more than 15 digits in a Cell
Type the following 16 digits number in a Cell
1.234567890123456
Excel truncates it to 1.234567890123450, the 0 at the back indicates a number (6) has been removed. Note that Excel does not round the number, it only truncates the last digit.
Case 2: Directly type a number more than 15 digits in a formula
Excel truncates the number before calculation.
=1.234567890123456+0.000000000000004
=1.23456789012345+0.000000000000004
=1.234567890123450
Case 3: Formula that results in infinite number
If you use formula to calculate an infinite number, the result is rounded.
=1/6
=0.166666666666667
Case 4: Calculation of infinite number that results from a formula
Excel calculates the whole formula and then round the result
=1/3+1/3 (or sum of two Cells)
=0.666666666666667
Solution to maximum number of digits
The maximum length of a text can contain 32,767 characters in Excel 2013. In our example, instead of typing 1.234567890123456 (16 digits), add a ‘ in front to convert a number to text
‘1.234567890123456
You can display the number in this way and you can even do calculation using text format, but Excel will truncate the number before calculation and return result of 15 digits. For example,
A1 = ‘0.666666666666666666666666666666666666666666 (more than 15 digits)
Now type the below formula
=A1+0.1
=0.666666666666666+0.1
=0.766666666666666
