Skip to Content

How To Get Rid Of E+(Number) In Excel

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?

A
19.997E+11
=9999^3
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.

A
1999700029999.00
=9999^3
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:

A
1999,700 m
=9999^3
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.