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 | |
|---|---|
| 1 |
9.997E+11
=9999^3
|
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:
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
|
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:
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 | |
|---|---|
| 1 |
999700029999.00
=9999^3
|
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:
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
.
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 | |
|---|---|
| 1 |
999,700 m
=9999^3
|
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!
Get Rid of E+ In Excel: 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 .
