This ebook is the culmination of 14 years of experience with 4,000 customers that have all transformed their business through low-code development.

However this engineering notation format gives a true zero number of 000.00E00 which surprised me (note the leading zeros), instead of simply 0.00E00.

Any other number wont show any leading zeros with the hashes in the format, for example 10 shows as 10E00 as expected and not 010E00.

Those leading zeros are no big deal, but its messing with my OCD.

Ive already solved this issue in my code constructing this string and applying it as a number format.

So really just asking if Im missing any other way to show a true zero value in engineering notation without the leading zeros Im using Excel 2016 (the Office 365 version) if it matters.

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

At the bottom of the page is the discussion on scientific notation.

Link.

While it doesnt 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 didnt know about.

Easier to removecustomise 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).

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 numbers magnitude.

Is there a way to force this (I could live with 000E0 if thats the price of engg notation 3 sig-figs).

Its important to leave the bold part of the conditional formula without the absolute references (i.e.

FIND(E,TEXT( B1,.E00),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.

Id say its a bit fiddly as any cutpaste 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.

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.

Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

The Eng-Tips staff will check this out and take appropriate action.

Marketing Engineering For Excel On Mac