×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-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!

*Eng-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

Formatting engineering notation in excel
2

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.

RE: Formatting engineering notation in excel

Hi,

How about

#.00E+00

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Formatting engineering notation in excel

(OP)
Hi Skip

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

No help

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Formatting engineering notation in excel

I came across this webpage on excel number formats a while ago and it solved many of my excel number formatting questions/issues.

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

(OP)
Thanks for the link. While it doesn't directly answer my query as the info deals with scientific notation which works as expected in my image, there are quite a few custom formatting tricks I didn't know about!

RE: Formatting engineering notation in excel

Try "Conditional Formatting" - if the cell value equals zero (or lies between a range of values that you consider to be "equal" to zero at your required precision), change the numeric format to "General" (or whatever you want zero to appear as) - this would yield the following formats:

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

(OP)
Thanks for that, another way to do it that I had not considered.

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

Somewhat related to this topic: My preferred format in a number of cases is engineering format (E3, E6, E9 etc) but with a constant number of significant digits displayed. Eg for three significant digits, 7.96324E8 would display as 796E6; 7.96324E7 would be 79.6E6. This requires the number of places after the decimal point to change depending on the number's magnitude. Is there a way to force this?

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

RE: Formatting engineering notation in excel

(OP)
steveh49, you can do this via a series of 3 conditional formats. See attached file for an example. I've coloured the three conditions so you can tell when each is triggered for a given number scenario.

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

Thanks very much, Agent666.

RE: Formatting engineering notation in excel

(OP)
No problem, note you could address the true zero state without leading zeros in the custom format as well using the custom format in my first post (just change the format to include the same positive/negative format and the zero format condition without the leading zeros).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

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

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close