Excel 2007 : Excel Scientific (Exponential) Function

I currently have a number of cells that have values in terms of scientific function. eg. 8E+8, 9E+10, etc.

Is there a way of formatting the cells so that they are all to the same exponential function? I.e. Change the cells so that they are all in terms of E+9?

The closest I've come is using what they seem to call "Engineering" format codes. If you use "###.#E+#", the number of placeholders before the decimal point (3 in this case) tells Excel that you want all the powers of ten to be multiples of three. Numbers will be displayed as 1E+3, 10E+3, 100e+3, 1E+6, 10E+6, and so on.

If you have, as suggested by your sample numbers, values between 1e8 and 1e11, you could use a format code of ########E+#, and they would all be displayed as xE+8. The thing to note is that, if any value rounds to less then 1e8, that will be displayed as xE+0. Any value greater than 1E16 will be displayed with 16 as the power of 10.

This is the closest I've come using number formats only. When I really want everything expressed to the same power of 10 regardless of magnitude, I've only succeeded by actually dividing the number by that power of 10 to effectively extract the mantissa. Then, in the heading for that column or some other way, I can note what the divisor was so I know what value is actually meant. The thing you have to be careful of is that, if this cell is used in later calculations, you have to remember that you divided it by 1E9 so you can multiply it by 1E9 when you use it in later calculations.

Thanks for the replies. I went with the second option and divided my answers by 1e9. Thought I did a safe thing by keeping my values to SI units but it turned out to be more of a hassle on Excel haha.

Thank you guys.