## Formatting engineering notation in excel

## Formatting engineering notation in excel

(OP)

Does anyone know any other solution to the following without going down the different formats for positive, negative and zero numbers in Excel?

For engineering notation formatting, you can put a custom cell format of "##0.00E+00", the double hash is a placeholder so only 10^0, 10^3, 10^6 etc are displayed vs scientific notation which would be 10^0, 10^1, 10^2, 10^3, etc without the hashes.

However this engineering notation format gives a true zero number of 000.00E+00 which surprised me (note the leading zeros), instead of simply 0.00E+00. Any other number won't show any leading zeros with the hashes in the format, for example 10 shows as 10E+00 as expected and not 010E+00. Those leading zeros are no big deal, but its messing with my OCD.

Is anyone aware of how to get a simple custom format of "##0.00E+00" to display a zero value correctly?

I've had to go down the "##0.00E+00;-##0.00E+00;0.00E+00" format route, but this gets a little messy as I'm using VBA to set the cell formats (a routine that toggles the formatting between 'normal' numbers and engineering notation for certain ranges). I've already solved this issue in my code constructing this string and applying it as a number format. So really just asking if I'm missing any other way to show a true zero value in engineering notation without the leading zeros?

I'm using Excel 2016 (the Office 365 version) if it matters.

For engineering notation formatting, you can put a custom cell format of "##0.00E+00", the double hash is a placeholder so only 10^0, 10^3, 10^6 etc are displayed vs scientific notation which would be 10^0, 10^1, 10^2, 10^3, etc without the hashes.

However this engineering notation format gives a true zero number of 000.00E+00 which surprised me (note the leading zeros), instead of simply 0.00E+00. Any other number won't show any leading zeros with the hashes in the format, for example 10 shows as 10E+00 as expected and not 010E+00. Those leading zeros are no big deal, but its messing with my OCD.

Is anyone aware of how to get a simple custom format of "##0.00E+00" to display a zero value correctly?

I've had to go down the "##0.00E+00;-##0.00E+00;0.00E+00" format route, but this gets a little messy as I'm using VBA to set the cell formats (a routine that toggles the formatting between 'normal' numbers and engineering notation for certain ranges). I've already solved this issue in my code constructing this string and applying it as a number format. So really just asking if I'm missing any other way to show a true zero value in engineering notation without the leading zeros?

I'm using Excel 2016 (the Office 365 version) if it matters.

## RE: Formatting engineering notation in excel

How about

#.00E+00

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Formatting engineering notation in excel

That's simply scientific notation, i.e. 10^0, 10^1, 10^2. You need the two extra '#'s to force the E+00 to go up in multiples of 3, aka Engineering notation.

Interesting if it was working for you, perhaps it displays differently on other versions of excel?

## RE: Formatting engineering notation in excel

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Formatting engineering notation in excel

Check it out.......maybe you can resolve your question. They have lots of examples. At the bottom of the page is the discussion on scientific notation......

https://www.exceltactics.com/definitive-guide-cust...

Link

## RE: Formatting engineering notation in excel

## RE: Formatting engineering notation in excel

123.46E-09

12.35E-09

1.23E-09

123.46E-12

0

instead of

123.46E-09

12.35E-09

1.23E-09

123.46E-12

000.00E+00

http://julianh72.blogspot.com

## RE: Formatting engineering notation in excel

More efficient to have the custom format though I bet. Easier to remove/customise a custom format than a conditional format, conditional format remains even if you want to change the underlying format to something else, which could get annoying (like wanting more decimal places displayed for example).

## RE: Formatting engineering notation in excel

(I could live with 000E0 if that's the price of engg notation + 3 sig-figs)

## RE: Formatting engineering notation in excel

It's important to leave the bold part of the conditional formula without the absolute references (i.e. the $ signs), otherwise it won't work.

FIND("E",TEXT(

B1,"###.###E+00"),1)=......How this works is it checks where the "E" is if the number is formatted in engineering notation, and formats the three possibilities based on this for three significant figures, where there are no decimal places the "." is not included. Note the three formulas are different.

i.e. formats as ##.#E+00, #.##E+00 or ###E+00 as required.

I'd say its a bit fiddly as any cut/paste of the cells also carries over the conditional formatting which can get a bit messy. Conditional formatting also can be quite slow on large data sets. I'm not aware of any way to do it using normal formatting.

Note also that I believe earlier versions of excel were possibly limited to 3 conditional format rules per cell, so doing this would obviously use up all the conditional formats. Unsure if latest versions have extended or removed this limit.

Another way to do it would be to setup a worksheet change event on the sheet in VBA and check specific cells, and reapply a custom 'numberformat' using VBA based on the number in that cell. Any change on the sheet would trigger the code to run again and reapply the correct formatting. You could automate it to some degree by being disciplined enough to have a style you applied to cells where you wanted the format to be 3 significant figures and then just loop through all the cells with this style applied on each worksheet (or specific cells) having their inputs changed.

## RE: Formatting engineering notation in excel

## RE: Formatting engineering notation in excel

## RE: Formatting engineering notation in excel

https://excelribbon.tips.net/T012874_Engineering_C...

Basically, type in a format of

##0.00E+#0