Custom Formatting Scientific Notation

**Shardling7** · 12-18-2012, 08:07 PM

Hello!
I am working on assembling some data into several tables, and I am having trouble getting Excel to format the data in scientific notation the way my instructor requires it. For example, I want the number 0.000008 to be formatted as 0.08e-4 instead of 8.0e-6 to match my other values. I need the powers to be the same so I can accurately compare and report those values in a table. If anyone could help me with this problem, I would greatly appreciate it!
Thanks!
P.S. I have friends who are formatting their data in Microsoft because we couldn't figure out an alternative way, but we have a lot of data! I would really appreciate any advice.

**shg** · 12-18-2012, 08:20 PM

You can't force Excel to format with a particular exponent. You can force it to do engineering formats (exponents that are multiples of 3), or exponents that are multiples of some other number. Or you could use a formula, but I'm not sure I see the value in that.

If you want to be able to visually compare them, use a fixed-point format.

**Shardling7** · 12-18-2012, 08:29 PM

Thanks for the quick reply!
I need the data to have the same exponents though, for data analysis and reporting purposes. Looks like I'll have to do it in word then...
Thanks again!

**MrShorty** · 12-18-2012, 11:45 PM

I'm not sure how it will be easier in Word. In Excel, you would need a "text" function that will build a text string for the number. Part will be to extract the necessary mantissa from the value, then part to concatenate on the exponential notation part. For putting everything on a 10^-4 basis:
mantissa=x/1e-4
exponent=-4
cell=concatenate(text(mantissa,"0.00"),"E",text(exponent,"00"))
Of course this makes a text string that isn't really a usable value, so if you need to use this value in other calculations, you will need to make sure you save the actual value somewhere.