Format a number with optional decimal places in Excel

"Apply Conditional Formatting for non-decimal numbers.

For example, A1 is the target cell.

Format A1 as ""###,###.00"". This will be used for decimal number.
Define Conditional Formatting for non-decimal numbers.

Condition: Cell Value equal to =TRUNC(A1).
Format: ###,###

Below is the result:

12 => 12
14.231 => 14.23
15.00000 => 15
17.3 => 17.30"

"My Answer removes the decimal point AND Removes 0 value

in conditional formatting write:

General;#;;@

This should do the trick."

"Remember that in Reporting Services, you can write an expression for the number formatting, too. In my situation, I used

iif(floor(Fields!numericFieldName.Value)=Fields!numericFieldName.Value,""0"",""0.##"")

as the number format expression.
This produced an Excel file with two cell formats, selected by whether or not the value was an integer."

"Are you / your users inputting values directly in the cells, or are they being populated by a formula or a macro?

If the cells are not being populated directly by a human, you could store the calculated values in a hidden range and then display formatted text to the user with a formula like this:

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,""0""),TEXT(A1,""0.0#""))

(where 'A1' is the cell being referenced)

The formula will display values like this:

-------------------------
|Original |Formatted|
|-------------+---------|
| 15 | 15|
| 14.3453453 | 14.35|
| 12.1 | 12.1|
| 0 | 0|
| -15.123 | -15.12|
| 1.004 | 1|
-------------------------

NB: The formula output is a text string, not a numeric, so:

The output defaults to being left-aligned.
You cannot use the output in any further calculations (instead, you should use the original cell being referenced)"