Excel Numeric Limitation

Excel’s Numeric Limitations

Excel users may be curious about the types of values that Excel can handle.
In other words, how large can numbers be? And how accurate are large
numbers?

Excel’s numbers are precise up to 15 digits. For example, if you enter a large value, such as 123,456,789,123,456,789 (18 digits), Excel actually
stores it with only 15 digits of precision. This 18-digit number displays as 123,456,789,123,456,000. This precision may seem quite limiting, but in
practice, it rarely causes any problems.

One situation in which the 15-digit accuracy can cause a problem is when entering credit card numbers. Most credit card numbers are 16 digits, but
Excel can handle only 15 digits, so it substitutes a zero for the last credit card digit. Even worse, you may not even realize that Excel made the card
number invalid. The solution: Enter the credit card numbers as text. The easiest way is to preformat the cell as Text (choose Home ➪ Number and
choose Text from the drop-down Number Format list). Or you can precede the credit card number with an apostrophe. Either method prevents Excel from interpreting the entry as a number.

Here are some of Excel’s other numeric limits:

Largest positive number: 9.9E+307
Smallest negative number: –9.9E+307
Smallest positive number: 1E–307
Largest negative number: –1E–307

These numbers are expressed in scientific notation. For example, the largest positive number is “9.9 times 10 to the 307th power” — in other words, 99
followed by 306 zeros. Keep in mind, though, that this number has only 15 digits of accuracy.