Table of Contents
To prevent Excel from automatically formatting numbers with E+11-, users can change the number format to a specific format such as “Number” or “Text”. This can be done by selecting the cells containing the numbers, right-clicking and selecting “Format Cells”, and choosing the desired format from the “Category” section. Additionally, users can also add an apostrophe before the number to force Excel to treat it as text instead of a number. This will prevent the automatic formatting with E+11- and display the number as entered.
Stop Excel from Formatting Numbers with E+11
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 Number Format to Prevent Excel from Showing E+11
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: