How to format cells to display two places after the leading zero in Excel?

Question

I'm new to excel and making a sheet that runs a nutritional analysis of a diet. Many of the foods analyzed contribute very little to the total value for a given nutrient, but the user needs to see where the total comes from.The raw values for individual foods are creating too much clutter, so I need to clean them up;

Requirements:

-display any number >1 to 2 dp

-display any number <1 to 2 places after the leading zero (e.g. 0.003948=0.0039)

-preferably not display decimal places after any whole numbers (i.e. 100=100 NOT 100.00)

Additional Info:

The values <1 range from 0.# to 0.00#

Thanks in advance for any help :)

Answer 1

If I understand what you want correctly, you can do this with a helper column containing a formula, and a custom format on that column.

The formula is there so that values less than 1 will be rounded to two significant digits.

Formula:

 =IF(A1<1,--TEXT(A1,"."&REPT("0",SigDigits)&"E+000"),A1)

And you can replace SigDigits with a 2

The number format:

[>=100]0;[>=1]0.00;General

In the above method, trailing zeros will not be displayed for numbers less than 1. In order to do that, you will need a VBA solution.