Store Large Numbers in Excel

The reason Excel can't store and display really large numbers is because it is not an inherently mathematical program; it's not meant for really complex mathematical equations that require exceptionally accurate calculations.

The Problem

Display the following number in Excel:

When we hit Enter, this is what we get:

The number is represented in scientific notation but, look to the formula bar and you will see that the last 4 digits have been turned into zeros:

The Solution

Convert the number to text.  You can do this by formatting a cell as Text before you put a number into it or by putting an apostrophe, same as a single quote, in front of the number.

Apostrophe Method

Input the number with a leading apostrophe like this:

It's as simple as that.

Format as Text Method

Select the cell where you want to input the number and then go to the Home tab and select Text from the Number drop down menu:

Input the number in that cell and that's it:

Important Note

When you store numbers as text, you cannot then perform the same types of calculations on it as you could if it was stored as a regular number.  If you try to perform calculations on this number, you will notice that the results will not be as accurate as you predict because, no matter what, Excel will still cut-off the end of the number and replace it with zeros.

Displaying numbers like this is only good for displaying the numbers.  Just because you can see the full number does not mean that you can perform accurate calculations with it.

Excel Number Limitations

Number Precision:                                    15 digits

Smallest Allowed Negative Number:     -2.2251E-308

Smallest Allowed Positive Number:       2.2251E-308

Larges Allowed Negative Number:        -9.99999999999999E+307

Larges Allowed Positive Number:          9.99999999999999E+307

Don't forget to download the accompanying workbook so you can see the above examples in Excel.