When a number is too long in Excel?

For example, if you were to place the number "1231231231234" into a cell, the resulting display would be "1.23123E+12" as soon as the Enter key was pressed.

Even if you increased the cell width to accommodate 12 characters, Excel will still display the number in scientific notation.

Thankfully, there's a way to display numbers in a format other than scientific notation! The trick is to reformat the cell properties.

Here's how to do it:

1. Select the cells that will hold the larger values and right-click the selection.
    
2. Select Format Cells.
    
3. In the Number tab, select the desired format (e.g., Number) and click OK.

Now when you enter the values that have more than 12 characters, they will be formatted as you specified -- and not using the dreaded scientific notation! ;-)

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.

A common problem is entering long numbers in Excel. You will know you have this problem if Excel does one of the following:

• Replaces some of the last characters of the long number with zero’s
• Seems to round long numbers to zero
• Refuses to accept the number you type- every time you type a number other than zero it reverts back to a zero
• you enter numbers that have 15 or more characters (think credit card numbers, ID etc)

The issue here is both an Excel constraint and/ or whether the ‘number’ is in fact a number and needs to be used as such.

Table of contents

• YouTube Dealing with long numbers in Excel
• The Excel Constraint with entering long numbers (15 or more characters)
• Numbers over 15 characters are rarely real numbers
• Format the cell as text to see the full large number
• Add an apostrophe in front of the number
• Related

YouTube Dealing with long numbers in Excel

The Excel Constraint with entering long numbers (15 or more characters)

Excel has a current limit of 15 characters in a number. What that means is if you want to enter a real number (see what we mean by real below) that has 15 or more characters ( so this is a quadrillion which is 1000 times more than a trillion) you are going to have to accept that Excel will round some of the numbers.

There is no real way of getting around this in Excel.

Shown below in cell B3 is what we want to see (a 20 character number).

• When you enter it you will see that Excel will automatically use scientific notation as per (1).
• If you format it as a number (2) you will see that all characters after the 15th character become zeros. You can type them as often a you like, they will always go back to being zero.
• This is even true within a formula (see 3). If we try and type the full number into the formula bar and add 1 to it, it will change the last characters to zeros

So if you are legitimately needing to use such big numbers with precision, Excel is probably not the right place.

Numbers over 15 characters are rarely real numbers

Unless you run a country with terrible inflation, really big numbers are often actually unique identifiers like ID numbers or credit card numbers. You never plan to do any mathematical calculation on them. You just want the number to be shown as it is without Excel rounding.

Effectively this means that the ‘number’ is actually text and should be treated as such. As mentioned after character 15, excel just sees 0’s. Below we have tried to type 1,2,3,4 from the 15th character and you will see that the 2,3,4 is replaced with zeros.

So below some ways to force a big number to be shown in full

Format the cell as text to see the full large number

Before you enter the number, format the cell/s as text. As shown below if we want to enter a large number in cell B1 we need to first format it as text.

This tells Excel to ignore its rules about how it treats numbers. This is important for large numbers but also for ‘numbers’ that start with a 0. If Excel sees a number that starts with 0, it assumes you made a mistake and drops the zero. By formatting as text you force it to show every character as it is (phone numbers have this issue).

If you are importing large numbers from a CSV or some similar input, you will have to make sure that on the import Excel knows to treat that column as text otherwise what you see in the CSV will differ from what Excel shows you.

Add an apostrophe in front of the number

The shortcut for the above is to put an apostrophe (‘) in front of the ‘number’. As shown below in the formula bar there is an ‘ in front of the number. You don’t see it in the cell (look at B3) but Excel knows that you now want to treat it as text.

Ironically sometimes Excel treats actual numbers as text and then treats unique identifiers as numbers. To make the text convert into a number you need to look at the Data Cleanup Course we offer.

Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.