It’s not easy working with Excel and really large numbers. In fact, Excel doesn’t do a great job with larger numbers.
Up to 15-digit integers, Excel is fine, but once you go over that, it quietly rounds numbers. Basically, data above 15 digits stop being accurate.
As a result, Microsoft hides that inaccuracy by displaying the number in scientific notation in its General format, which obscures any rounding that may have happened.
Numbers like those we’re discussing are far larger than most people will ever require. Excel users should nevertheless be aware of this issue and, at least according to us, it should be made clearer to them. This isn’t ‘best practice’ regardless of why Excel is changing the number entered into it.
Rounding
Here is a worksheet of large prime numbers. The second column displays the text representation of the number or Mersenne prime formula used to generate the number. The third and fourth columns in the table display that number in Excel’s General format and then as a comma-separated integer.
There are a few things we need to point out, showing the way Excel operates.
- Both 15- and 16-digit prime numbers at the top of the rows, read the same General format 1E+15, despite being completely different numbers.
- The 16-digit prime number text format was originally “10000000000000037” however, Excel automatically changed this to end in 030 instead of 037. This is the effect of Excel’s hidden rounding of large values. It will change it immediately and without warning.
- This also applies to Mersenne 61 and above. We can notice all the zeros at the end of the value. However, the correct value of Mersenne 61 is actually 2,305,843,009,213,693,951 – a difference of a measly 3,951.
- The last two Mersenne’s (521 and 607) have too many digits to display (157 and 183 digits respectively).
Displaying large numbers
If you do need to display a large number in Excel, it’s best to do it as text. You can’t use it as a number (if you do, it will be rounded) but it can be displayed just like any other text.
In an Excel cell, type an apostrophe then type in (or paste) the number. The apostrophe tells Excel that the following is to be treated as text instead of it attempting to format it automatically.
So in our example, the text has not been replaced by 030 instead of 037 – because the apostrophe was applied.
Use the Value() function to convert text into a number (rounded) for calculations.
It’s understandable that Excel has a size limit on numbers because it’s in the nature of how computers work. If you’re working with big numbers, keep in mind that Excel will start rounding if they get too big.
See Prime Number is too big for Excel
