Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Store Large Numbers in Excel
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.
Question? Ask it in our Excel Forum
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situat...
Macro: This free Excel macro formats selected cells in the Scientific number format in Excel. Thi...
Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...
Macro: This free Excel UDF allows you to output the color of a cell in text format or as that col...
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command. This will save ...
Tutorial: In order to combine a cell that has a date with a cell that has a time, using a Macro and ...