How to make Excel show full number instead of E

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Inhaltsverzeichnis Show

How To Fix A Cell That Shows ###########
Increase Width Of Column
Decrease Font Size
Format Number In Thousands, Millions Or Billions

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(Sale Ends Friday May 6)

I'm aware of the age of the question, but this is the one I've landed after google search and which did not answered my question (why formatting as text doesn't work and why number is cut down to 15 digits after format to text).

Short answer is: you can't work on your number the way you like AFTER you entered the numeric value and tapped Enter. The moment you do that the number will be truncated to 15 digits and presented in as exponential.

This is not a bug, it's a feature.

Anything you do after that will be using the truncated value, so no formatting trick will help. However, you can use the Text to columns option to convert exponential notation to text (Click on column header, click Text to data, then Delimited, Next, untick all delimiter boxes, Select Text in Column data format and then Finish) to have the values converted to text and immediately displayed as 15 digit number. But, it will be only 15 digits, so if you had longer number, rest is lost.

In order to have the number in the spreadsheet exactly the way you typed it in you have to store it as text. So you have two ways of doing that, but they will work only BEFORE/DURING editing:

you need to precede it with apostrophe or format cells as text before typing/copying values into it,
you have to format the target column as Text before input.

If you're using method 1 and it "sometimes doesn't work", then, sorry, you either do something wrong or you have some sort of autocorrect on (if you use apostrophe and large number >15 digits Excel treats it as a wrong value and flags cell with warning message, so this remark is not personal nor critique).

Method 2 is very useful, because it works with copy&paste, even with large datasets.

Other way to do it in bulk numbers is to import values from text file. I dare say it's the only way for most situations. And be sure to import the file, not just open it, as Excel treats csv type like it's native format and trims large numbers to 15 digits as a matter of course.

Now, to the last issue. If you enter large numerical value in Excel cell formatted as text it will still be displayed in scientific notation as long as the cell is not wide enough. It's annoying, but side effect of some Excel internal algorithms. Just make the cell wider and you'll see full value every time.

When using large numbers in Excel or any other spreadsheet application, such as Google Sheets, some cells may display a number in scientific notation like 9.991E+35. How do you get rid of that E+n bit, where n is some number, in the cell?

Large number in cell

The easiest way to change a number being displayed as E+n (where n is a number) is to right-click on the cell, click on the context menu item Format cells and then in the Number tab and the Category section select the option of Number.

Here is what the window looks like on the Mac version of Excel:

To remove the E+n on a cell, change the format to Number

How To Fix A Cell That Shows ###########

Once you change the format of the cell to Number you might then see the cell you’ve selected change into something like this:

	A
1	################ =9999^3

A number larger than the column width

This means the number in that cell is too large to display according to the width set by the column.

There are several ways to handle this type of display problem: increase the width of the column, or decrease the font size of the cell’s contents, or change the format of the number.

Increase Width Of Column

To increase the width of the column simply hover your mouse cursor over the right-hand boundary of column A, and as you hover your mouse you will see it change to a cursor containing an arrow pointing left and right, like here:

You can resize a column when you see your mouse cursor change to this

Once you see your cursor change to this type then drag the column right to increase the width of the column to be able to display the large number. Or you can double-click when your mouse cursor does change to the above and Excel will automatically increase the size of the column to match the width of the contents of the column.

The column will have the correct width when you can properly see the number

What if you cannot widen the width of the column?

Decrease Font Size

Another option to display the number contained within the cell is to reduce the font size. By making the font size smaller it means you can display more content.

Simply click on the cell and find the font-size option in the Home ribbon, or if you right-click on the cell and select Format cells click on the Font tab and then in the Size area change the number to be smaller than what it currently is:

Decrease font size, here I’m making the cell size 8

If making the font size isn’t the preferred option for displaying the large number, then the third option is to try and change the format of the number by applying an abbreviation.

Format Number In Thousands, Millions Or Billions

If lengthening the width of the column is something that causes issues with the look and feel of your spreadsheet, then another option of properly displaying the numbers contained in the cells with these large numbers is to shorten them using a custom format.

For example, you could represent each number using m for millions, k for thousands, b for billions (etc).

To edit the format of the cells containing these numbers, and if reducing them by thousands, millions or billions still makes the data meaningful then right-click on the cell containing the large number, then click on Format cells then in the Number tab click on the Category option Custom.

To format a large number select Custom and in the Type field enter the appropriate abbreviation

In the Type field enter the abbreviation needed. In my case I’m going to abbreviate the number to be represented into millions, therefore, enter the following code:

#,##0,," m";[Red](#,##0,," m");-;@

The result of this type of custom number format produces something like this:

Format of large number into millions

If you prefer to format numbers into other representations, then go to our other article and learn how you can abbreviate numbers by thousands, millions, billions, trillions and even gazillions!

Summary

Most spreadsheets will automatically convert large numbers in cells using scientific notation as this helps to display the number rather than just a cluster of hashes. If this is not ideal for your use case and if you prefer to still see the number to its fullest then it is just a simple matter of changing the format of the cell.

Once a cell has been formatted to a Number format you may come across the next issue where the cell only displays hash symbols, like #########. To fix this issue simply widen the width of the column, or decrease the font size, or apply a custom number format to fit all of the numbers contained in the cell.

Finally, there are other alternatives to displaying large numbers using scientific notation, and if you want to display numbers in other abbreviated formats such as thousands, millions, billions, trillions and gazillions check out our other article.