Whenever you type a number into Excel that contains more than 11 digits, Excel will automatically format the number using E+11 at the end of it.
Fortunately there are two ways to prevent Excel from doing this:
Method 1: Use Number Format
Method 2: Use TEXT Function
The following examples show how to use each of these methods in practice.
Example 1: Use Number Format to Prevent Excel from Showing E+11
Suppose we type the number 12345678911 into Excel:
This number contains exactly 11 digits so Excel is able to display each digit.
However, suppose we instead type 123456789111 into Excel:
Excel now formats the number as 1.23457E+11 since this number contains 12 digits.
To prevent Excel from doing this, we can click cell A1, then click the Number Format dropdown menu from the Home tab along the top ribbon and then click Number from the dropdown menu:
This will force the value in cell A1 to be displayed as a number in which all of the digits are shown and E+11 is no longer displayed at the end of the number:
Example 2: Use TEXT Function to Prevent Excel from Showing E+11
Once again suppose we type 123456789111 into Excel:
Excel formats the number as 1.23457E+11 since this number contains 12 digits.
Another way to prevent this from occurring is to type the following formula into cell B1:
=TEXT(A1, "0")
This formula uses the TEXT function with the argument “0” to format the number in cell A1 in such a way that all digits are displayed.
Note: You can find the complete documentation for the TEXT function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Round to Significant Figures in Excel
How to Remove Digits After Decimal in Excel
How to Convert a Fraction to a Decimal in Excel
