7

In Google Sheets when I enter

10E-12

in a cell formatted for scientific notation, it automatically turns the number into

1E-11

Though technically correct, this is really annoying when comparing numbers between cells. Is there a way to specify the power to which the number should be raised in Google Sheets?

3 Answers 3

9

To specify power of exponent, such as to powers of 3, 6, 9, etc, specify the number of places in the format.

e.g. ##0.00#E+##

Note that there are 3 places on the left of the decimal and 3 places on the right of the decimal.

The powers will increase or decrease by multiples of 3.

The exponential display can be formatted as you wish with 0 or #.

enter image description here

1
  • Microsoft Excel seems to obey these formats, too. ##0.00#E+## ##0E+# ##0E# etc Feb 14 at 10:25
2
  1. Select the cell(s)
  2. Click on Format > Number > More formats > Custom number format...
  3. On the box write something like #0.0E+00
  4. Click Apply button

Note: The leading # is to set an optional digit

Reference

3
  • 1
    Thank you very much! Is there also a way to go in the other direction? (i.e. if I want 8E-13 to display as 0.8E-12)
    – Bunji
    Jun 14, 2019 at 16:38
  • 1
    @Bunji Try 0.0E+00 Jun 14, 2019 at 16:39
  • Unfortunately, I don't think this works. It still shows up as 8.0E-13
    – Bunji
    Jun 16, 2019 at 17:05
0

1.79769E+308 is the maximum number that Google Sheets is able to output. Respectively -1.79769E+308 is the negative value. Anything higher than that will output into error or gets down-corrected by Google Sheets.

Raising on the power in Google Sheets is done with ^ symbol like:

=25^7
=999^99

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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