In Excel format number with optional decimal places

Simple: I have numbers in cells in excel. I want the numbers formatted so that if they have decimal places they show to a maximum of 2DP and if they have no decimal places it doesn't show any.

For example.

• 15 should be formatted as 15 NOT 15.00
• 14.3453453 should be formatted as 14.35
• 12.1 should be formatted as 12.1
• 0 should be formatted as 0

The closest custom format code I've come up with is 0.##. Unfortunately this formats 15.00 as 15. (note the extra decimal point).

Edit: To further complicate the issue, the spreadsheet is a result of an export from SQL Server Reporting Services. So no macros are possible. Oh well, it looks like 0.## is my best bet, and they can just live with the extra period.

-

migrated from stackoverflow.comNov 1 '10 at 12:39

Use the following custom format:

```[=0]0;.##
```

This is basically a conditional format. Pseudocode:

```If CellValue == 0
Display 0
Else
Display CellValue to up to 2 decimal places
```

Edit: I ran this through a few examples in Excel and got these. Does this meet your needs?

```     -------------------------
|Original     |Formatted|
|-------------+---------|
|       0     |        0|
| 12.1234     |    12.12|
| 12.1278     |    12.13|
|    12.1     |     12.1|
| -15.123     |   -15.12|
|   -24.9     |    -24.9|
-------------------------
```

Edit 2:
The table showed up fine in the preview, but it got eaten when I posted.. how odd.
I recreated it in <pre> tags....

Edit 3:
Stupid missing test cases... looks like this doesn't solve the trailing decimal problem either. Sorry for getting your hopes up.
My quick searches showed a couple people saying that this one isn't solvable through normal format codes. It needs to be VBA. Sorry to get your hopes up.

-

Solution: 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.

• Input => Formatted

12 => 12

14.231 => 14.23

15.00000 => 15

17.3 => 17.30

Hope this can help, although late.

-

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

-
Yes, both TRUNC(x,2) and ROUND(x,2) will do the job (depending on what kind of rounding method you're after). You then just need to set the cell formatting to "General" and the decimal places will only be displayed if needed. (I vote to mark Droj's post as the answer.) – Simon Jun 13 '11 at 9:46

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 can't believe no one figured this out!

It's so simple, no VBA necessary, here are the steps:

1. Right click your cell, choose "Format Cell"
2. Select "Custom"
3. Enter "0.####" (add more or fewer #s to control the maximum number of decimal places)
4. Click OK
5. With the cell still selected, use "Conditional Formatting" (may be a menu item in Format or button in Home depending on your Excel version)
6. Add a new rule, based on the formula "=MOD(H32,1)=0"

edit - better formula is "=MOD(ROUND(H32,2),1)=0" with the '2' being the desired number of decimal places. previous formula leave trailing decimal point if the number rounds to an integer.

Replace H32 in the formula with the ID of your cell, but MAKE SURE THERE ARE NO \$ SIGNS! (No \$ signs ensures that you to copy the format to other cells)

7. For the format of this rule, select the number tab, choose "Custom"

8. This time, enter the formula "0"
9. Click OK enough times to return to the sheet (varies among excel versions)

There you go, enjoy responsibly! If you want to copy the format to other cells, remember that you can copy the cell and use "Paste Special" with the "Format" option.

-
 Could you clarify if this only applies to certain (or more recent versions) of Excel? I am still using 2003 (and I suspect an embarrassing number of others are as well) and when I go to Conditional formatting, I have no option to modify the number formatting, only Font, Border, and Patterns. Thanks – SSilk Jan 21 at 20:19 I just tried this approach on Excel 2010 and it worked fine. It will not work with earlier versions of Excel (at least that's what my "compatibility mode" checker in Excel 2010 tells me). – Thomas Feb 12 at 19:11

Format cell as 'General' then under data validation restrict values to decimals

-
 If you need to avoid adding formulas (and probably a new column) to your spreadsheet then Fred's suggestion may be the best. – Simon Jun 13 '11 at 9:55 This will not allow people to enter numbers with more than the visual limit of decimal digits. – Thomas Feb 12 at 19:14

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.

-

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)
``````
-

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)
-

Further to Luke's great answer above here is a variation based on his work. I didn't want to have a zero in the cell if there was no value. Leave your cell format as general and used this formula:

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

Again where A1 is the cell being referenced.

-

Using the `Round` function to provide significant figures. This does not change the number of decimal places displayed however. Just format the cell with `general` number format though.

``````ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue)))
``````
-
 Why all the complicated INT(LOG... formula? Why not just ROUND(value,2) and format as General? – Simon Jun 13 '11 at 9:54