Store Large Numbers in Excel

Add to Favorites

I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't show numbers that are too big but, there is a way around this.

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:

1234567890123456789

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:

1234567890123450000

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:

'1234567890123456789

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Convert Numbers Stored as Text to Numbers in Excel
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situati...
Format Cells as a Scientific Number in Excel Number Formatting
Macro: This free Excel macro formats selected cells in the Scientific number format in Excel. Thi...
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
Macro: This free Excel UDF allows you to output the color of a cell in text format or as that col...
Keep Leading Zeros in Numbers in Excel - 2 Ways
Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...
Quickly Create a Huge List of Numbers in Excel
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command.  This will save y...
Join a Date and Time into a Single Cell using VBA Macros in Excel
Tutorial: In order to combine a cell that has a date with a cell that has a time, using a Macro and ...