Microsoft: Office FAQ

Best of Excel

I searched high and low for the answer to this question, finding nothing, I have posted the solution here so hopefully others will find it quicker in the future.

Is there a way in Excel to format cells in Engineering Notation?

As it turns out Engineering Notation IS available by selecting Cells Format Custom and using the ##0.0E+0 format option ... to increase the number of decimals just add more zero's ie ##0.00E+0

What and Why should anyone want to use Engineering Notation?

This may be clear to some people but for the everyone else ... Engineering Notation is similar to Scientific Notation in that it has the ability to represent large numbers using numbers we are comfortable with. For example a Hard drive specified as 320GB is actually representing a number that is 320,000,000,000 or 320 billion bytes (lets not go into the K factor here (K representing 1024 bytes)) ... 320G is actually already in Engineering Notation (where G is understood to be GIGA or 9 zero's) 320 * 10 ^ 9 (EXCEL formats this to be 320.0E+9 where E represents 10 ^ 9 ) ... this same number in Scientific notation would be 3.2 * 10 ^ 11 (Excel 3.2E+11)

Basically Scientific Notation ranges from negative 9.99 to plus 9.99, where Engineering Notation ranges from negative 999.99 to plus 999.99 ... the E and or * 10 specifies how many places to the (positive) right or (negative) left the decimal moves ... so 320.0E+9 means we have 320 + 9 more 0's ... because it is positive it means the Zero's are to the right of the decimal. A number like 320.0E-9 would indicate the decimal has to move 9 places to the left so 320.0E-9 would actually be .000000320

Putting large numbers into notation format gives us the benifit of viewing smaller numbers which also make it easier to manipulate (if you have to do any calculations consider multiplying 125,021,481 * 0.5 you can do this with Excel and or a calculator easy enough or in your head as 1.25E8 * .5 = 1.25E8 / 2 = .625E8 = 62.5E6 ). Engineering Notation takes it one step further by making it easier to group numbers into familiar units: