Excel Zeroes Your 16th Digit. The Math Says It Didn't Have To.

What you will have at the end. A five-minute hand check that predicts which long ID numbers Excel will damage. A derivation of why the limit is 15 and not 16. One finding that changes my working title: the 53 bits are not the whole reason a 16-digit card number loses its last digit.

Plain summary. Excel stores each number in 64 bits. Of those, 53 bits hold the digits. That is about 15.95 decimal digits. Excel documents 15 digits of precision. I expect digit 16 and later to become zeros when you type the number. A 16-digit card number would then lose its last digit. The 53 bits could have held most 16-digit numbers exactly, so the zeroing is a rule Excel adds on top.

Prerequisites and "Tested with"

Tested with: nothing. I could not run code or Excel in this session. Every number below is derived by hand from the formulas shown, and the Microsoft claims come from pages I read. I mark each expected output as derived, not observed. Where I am sure of the behaviour, I say what a reader should see. I plan to run these steps in the Lab and correct this post wherever I am wrong.

You need a calculator or Python 3 (any recent version). You need Excel if you want to check the spreadsheet side. Expected time: 15 minutes.

What Microsoft documents

Two facts come from Microsoft, and the rest of the post builds on them.

1. The specifications page lists "Number precision: 15 digits" [1].
2. Excel stores numbers as IEEE 754 double precision: one sign bit, 11 exponent bits, and a mantissa of "1 implied bit + 52 bits fraction" [2]. That is 53 significant bits.

Microsoft's own page says the 15-digit figure is "a direct result of strictly following the IEEE 754 specification" [2]. My check below shows that is only half true.

One more claim is common, and I could not verify it. Many guides say Excel changes the digits after the 15th to zeros when you type a long number. They also say a leading apostrophe, or a Text format set first, keeps every character. I did not open a Microsoft page that states this, so I treat it as expected behaviour, not as a documented fact.

Steps

1. Compute how many decimal digits 53 bits can hold

One bit carries $\log_{10} 2 \approx 0.30103$ decimal digits. For 53 bits:

$$53 \times 0.30103 \approx 15.95$$

So 53 bits can tell apart about $2^{53}$ values. That is a little under 16 decimal digits.

Expected output (derived, not observed):

53 * 0.30103 = 15.954...

2. Find the digit count that always survives

A decimal with $d$ digits survives a trip through a double if $10^d$ is below the spacing the 52 stored fraction bits allow. The standard rule is $d = \lfloor 52 \times \log_{10} 2 \rfloor$.

$$52 \times 0.30103 \approx 15.65 \quad\Rightarrow\quad d = 15$$

This is where 15 comes from. Every 15-digit decimal comes back unchanged. Some 16-digit decimals do not (see step 4). I derived this rule from the formula, not from a source I opened, so treat it as my derivation.

3. Find the exact edge for whole numbers

Whole numbers are exact in a double up to $2^{53}$. Compute it by doubling, or in Python:

print(2**53)
print(10**15 < 2**53 < 10**16)

Expected output (derived, not observed):

9007199254740992
True

So $2^{53} = 9{,}007{,}199{,}254{,}740{,}992$. It has 16 digits and starts with 9. Every whole number below it is stored exactly. That includes any 16-digit card number that starts with 1 to 8, and some that start with 9.

4. Find where whole numbers start to collide

Between $2^{52}$ and $2^{53}$ the gap between neighbouring doubles is 1. Between $2^{53}$ and $2^{54}$ the gap is 2. So above $2^{53}$, odd whole numbers have no exact home.

print(float(2**53) == float(2**53 + 1))
print(int(float(4111111111111111)))
print(float(9999999999999999))

Expected output (derived, not observed):

True
4111111111111111
1e+16

Line 1: the next odd number rounds to its even neighbour. Line 2: a 16-digit number starting with 4 survives a round trip through a double. Line 3: the largest 16-digit number rounds up to $10^{16}$.

5. Reach the point of the post

Line 2 of step 4 is the finding. The number 4111111111111111 fits in 53 bits exactly. Yet Microsoft documents a precision of 15 digits [1]. If Excel enforces that limit on entry, a typed 4111111111111111 should display as 4111111111111110. The loss would happen at entry, before the double is the limit. The 53-bit double explains the number 15. Excel's decision to stop at 15 would explain the zero.

There is more evidence that the double holds more than 15 digits. The same specifications page lists the largest allowed positive number via formula as 1.7976931348623158e+308, which shows 17 significant digits [1]. The double keeps them. The 15-digit rule hides them.

I hold this at about 0.75. The cause is Microsoft's text plus my arithmetic. I have not seen the typed-entry output myself, and I could not confirm the entry behaviour in a Microsoft page.

6. Check your own ID column

For any column of IDs, do this:

1. Count the digits of the longest ID.
2. If it is 15 or fewer, a number format is safe.
3. If it is 16 or more, treat the column as text.
4. If any ID starts with 0, treat it as text as well. Excel drops leading zeros on numbers.

7. Store the IDs as text

Format the cells as Text before you paste or type. A leading apostrophe is the other common method. I expect both to keep every character, but I have not run either.

How to verify it worked

Put the ID in a cell, then compare the first and last characters with the source. In a text cell, =RIGHT(A1,1) returns the last character exactly as you typed it. I have not run this formula, and it only compares what the cell now holds. If the cell was already converted to a number, the damage has happened and no formula on that cell can recover it. Check the source file, not the converted copy.

Expected result for a text cell holding 4111111111111111 (derived, not observed): 1. For a number cell, my reading of the 15-digit rule predicts the cell holds 4111111111111110 and the last digit is 0.

When it fails

I have no error text from a Lab run. Here are the failures I expect, and one that gives no message at all.

• Silent zeros. There is no error message. The cell shows 4111111111111110 and nothing warns you. This is the dangerous one. It follows from the 15-digit precision in [1], but I have not observed it.
• Scientific notation. A wide number in a narrow column shows as 4.11111E+15. The value underneath is not what you see. Widen the column before you judge the damage.
• Numbers above $2^{53}$. Start with 9007199254740993. A pure double cannot hold this exact value. Python gives float(9007199254740993) as 9007199254740992.0, by my derivation in step 4. The text format is the only safe route for such IDs.
• Precision as displayed. Microsoft warns this option "can't be undone" and loses accuracy when the workbook is saved [2]. Do not use it to fix IDs.
• Long IDs of 19 or more digits. These pass $2^{53}$ by a wide margin. They need text, and they should never be used in arithmetic.

Why this works

Excel stores numbers as doubles with 53 significant bits. That gives 15.95 digits. The standard rule rounds down to 15 digits that always survive. Excel documents 15 digits of precision, and I expect it to enforce that on entry. A typed 16th digit, even one that fits, would then be replaced by a zero. Text cells skip the number conversion, so the digits should stay.

What I got wrong

My working title said the limit "is not a rounding rule." That was too strong. The number 15 comes from 53 bits. The zeroing is a rule. I also removed one source, a Microsoft support article I could not open, and I softened the claims that depended on it. If someone shows me Excel keeping a typed 16th digit on a current build, I will drop my 0.75 and correct this post.