Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Office FAQ

Best of Excel

How to Format a Cell for Engineering Notation by BJZeak
Posted: 9 Aug 08

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:

0.000000000001  1.0 E-12 = p, Pico
0.000000001     1.0 E-9  = n, Nano
0.000001        1.0 E-6  = u, Micro
0.001           1.0 E-3  = m, Milli
1.0             1.0 E 0
1000.0          1.0 E 3  = K, Kilo, Thousand
1000000.0       1.0 E 6  = M, Mega, Million
1000000000.0    1.0 E 9  = G, Giga, Billion
1000000000000.0 1.0 E 12 = T, Tera, Trillion

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close