Format a number with optional decimal places in Excel

Alternatively, you can solve the "optional" decimal point problem using the following solution:

Number Format: General;[Red](General)

This will add the decimal place and fractional value accordingly, while formatting negative numbers in a more legible way.

As for the poster's original question, you still need to round/truncate the "extra" decimal points using a formula. However, this is a simple formula of =ROUND(<value>,<desired decimal places>) that is not extremely computationally expensive.

Examples:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)

Apply Conditional Formatting for non-decimal numbers.

For example, A1 is the target cell.

1. Format A1 as "###,###.00". This will be used for decimal number.

2. 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

Excel custom formats can provide a partial answer

Custom formats for numbers in Excel are entered in this format:

• positive number format;negative number format;zero format;text format

One format that comes close to your requirement, but leaves in the decimal place for numbers with no decimals is:

#,###.??;(#,###.??);0

Example:

• 15 is displayed as 15.
• 14.3453453 is displayed as 14.35
• 12.1 is displayed as 12.1
• 0 is displayed as 0

I ran into this recently in Excel 2007 and just ended up using the 'TRUNC' function in the value cells:

  value  =TRUNC(B5,1)
      0      0
      5      5
    5.4    5.4
  65.43   65.4
765.432  765.4

Worked exactly the way I wanted it to...