Excel maximum number of digits workaround

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

Outbound References

https://support.microsoft.com/kb/78113?wa=wsignin1.0

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *