1

I want to see big numbers in same power of scientific notation in microsoft excel

for example, I want to see these numbers in excel cells

1.23E+7          1.45E+10          1.36E+12

as below:

1.23E+7        1453.11E+7      136445.11E+7

(all with E+7 power)

2

To my knowledge you cannot fix the power using the default formatting (though I'm happy to learn I'm wrong). What you can do instead: use a formula next to it to convert it to your format:

=TEXT(INT(A1/10^5)/10^2;"0.00")&"E+7"
  • in my case, with greek international settings i.e. comma for decimal separator, this was the only thing that worked : =TEXT(A1*10^7;"0,00") & "E+7" – hello_earth Jan 30 '16 at 15:42
2

No, not in Excel and still retaining it as a numeric value. But, if you are willing to accept "E+n" where n is a multiple of 3 you can use custom number formats such as:

#,##0.00,"E+3"
#,##0.00,,"E+6"
#,##0.00,,,"E+9"

Each comma appearing after the decimal point shows the output divided by 1000. Using custom number formats has the advantage of keeping the real numeric values unmodified and suitable for further calculations.

0

I was unable to get Excel to meet your requirements without using additional formulas in cells to strip down and extract data on the values.

Pointer towards solution:
* Make A1 the cell containing your specific value.
* In cell B1: Use =A1/(10^7)command . This will give you the value you can manipulate.
* Next: In cell C1, use the =VALUE(B1)&"E+07" command.
* You should now have a standard power-of-7 display.

It is the beginning for the standardization of your sheet for large numbers. The rest of your activities will be standard manipulation to x-numbers before comma and y-numbers after comma.

0

This method can be arduous depending on how many numbers you have to modify. If your numbers differ largely in magnitude you will have to format each number individual. But basically, adding/removing the "#" sign in the custom number formatting, as shown below, allows you to manipulate the order or magnitude displayed. Excel treats the "#" as a place holder for a significant digit. Hope this was helpful!

14531111111        #######.00E+00        1453.11E+07
1364451111111      #######.00E+00      136445.11E+07
0

This seems to work for me (converts numbers to milli, micro, nano, pico...). Strip off the ' quotes; they were required to get the display right in this post.

'##0.000E+00'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.