Search code examples
exceldecimalnumber-formatting

Excel number format to only show decimals when necessary

How can I, without using formulas, show integers as integers, but decimals limited to a specific number of decimal places?

E.g. show:
1 as 1
12 as 12
but 1.23456789 as 1.23

The number format 0.## is close but it shows 1 as 1. etc.

Solution

  • If acceptable, you may use the formula not in the sheet but as a condition for Conditional Formatting.

    When your data is at A1:A4 as the sample on screenshot, we should create 2 Formatting Rules:

    1. Formula is: =MOD($A1,1) = 0 Applies to: =$A$1:$A$4 Format the number as no decimals from the format of this rule.
    2. Formula is: =MOD($A1,1) <> 0 Applies to =$A$1:$A$4 & Format the number to show 2 decimals.

    Actually the 2nd condition is optional. You may format the whole column with 2 decimals and conditional format with the first rule so only the integers will have no decimals.

    Please see my sample screenshot:

    enter image description here