Custom format - display n decimal places except when there are fewer decimal places

In Excel we can customize the number of decimal places to display. For example if we set it to 2:

2.2556 > 2.26
20 > 20.00
7.1 > 7.10

In practice, this is highly undesirable. The most common requirement (in office settings at least) is to display as many decimal places as there is, but not exceeding a limit. For example, if that limit is 2, we should have:
2.2556 > 2.26
20 > 20
7.1 > 7.1

Because nobody wants to see the extra zeros when it's taken for granted that they are there (for the case of 20 and 7.1 above)

Note: I know the "General" format tends to keep whatever you typed, so case 2 and 3 above would be fine, but for case 1 (2.2556 I would have to manually format it to 2 decimal places)


Thanks!

In practice, this is highly undesirable.

I disagree - it is far easier to compare values scanning down a column that have the same number of decimal places than not.

I don't think what you want can be done without VBA, unless the values in question are the result of a formula. I think this is the closest you will get with custom number formatting:

0.#0

This leaves an orphaned decimal point:

0.##

However, if you use 0.## combined with a conditional formatting rule:

=INT(A1)=A1

then you can force numbers with no decimal places to show as General format.

Use this as the custom format:

0.##

Thanks both of your for the solutions. Neither solve my problem perfectly but I appreciate the input.