# Big numbers rounded in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Excel and really large numbers – they don’t play nice together.

When we wrote about large prime numbers in Excel we forgot that Excel doesn’t do a good (some might say honest) job with large numbers.

Sorry about that – mea culpa.

Excel is fine with integers up to 15 digits but once you go over that, it doesn’t error but quietly rounds values. In other words a 16 digit number or more stops being accurate.

Microsoft hides that inaccuracy by displaying the number in scientific notation in its General format which has the effect of obscuring the rounding that’s going on.

As we mentioned in our previous article, this isn’t to diss Excel. The numbers we’re talking about are way bigger than most people would ever need. Nonetheless, it’s something Excel users should be aware of and, in our opinion, should be made clearer to users. Excel is changing the number entered into and, for a spreadsheet, that’s hardly ‘best practice’ regardless of the reasons.

### Rounding

Have a look at this worksheet of large primes. The second column shows the text rendering of the number or Mersenne prime formula used to generate the number. The third and fourth columns show that number in Excel’s General format then as a comma-separated integer.

A few things to note:

• The 15 and 16 digit primes near the top read as being the same in General format ie 1E+15 when they are different numbers.
• The 16 digit prime is rounded by Excel (ending with 030 instead of 037). That’s the effect of Excel’s hidden rounding of large values. When you type ‘=1000000000000037’ into the cell, Excel changes it immediately and without warning to ‘=1000000000000030’
• The same applies to Mersenne 61 and higher. Notice all the zeros at the end.

• The correct value for Mersenne 61 is 2,305,843,009,213,693,951 – difference of a mere 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.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address