How to Format Excel Cells for Engineering Notation?

In the engineering disciplines int's nice to be able to have values formatted in easily readable engineering notation, where the exponent is a multiple of three. How can this be accomplished in Excel?

2 Answers

In the cell formatting options create a new custom type for ##0.0E+0.

To display a value with prefixes instead, one might do some formula massaging and end up with e.g.

=LEFT(TEXT(A1,"##0.0E+0"),FIND("E",TEXT(A1,"##0.0E+0"))-1) & MID("npum kMGT",5+VALUE(MID(TEXT(A1,"##0.0E+0"),FIND("E",TEXT(A1,"##0.0E+0"))+1,100))/3,1) &"s"

... for a value in A1, that represents a time in seconds (as indicated by the last "s" in the formula).