Excel – Format a number with optional decimal places in Excel

microsoft excelworksheet-function

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 two, 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).

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.

Best Answer

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)

Related Solutions

Excel: conditionally format a cell using the format of another, content-matching cell

This uses cells in A1 to set the condition for cells in D1:D9 - alter ranges to suit your needs:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" and Sh.Name="Sheet1" Then
    Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color
    Sheets(1).Range("D1:D9").FormatConditions(1).Interior.Color = Target.Interior.Color
End If

End Sub

After changing the color, you will then have to change the cell by editing it, and pressing enter (so the change event is triggered)
the format conditions are ordered by the order the rules are applied. There are a lot more changes that could be applied, just add another .FormatConditions(1).Whatever = Target.Whatever to the code

This code does not set up any conditions, it will just alter the one(s) that are there. The conditions are numbered in the rule order displayed on screen

if you don't want to use conditional formatting, and just color the cells, then you can loop over each cell this way:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Or Sh.Name <> "Sheet1" Then Exit Sub
' exit if not in key range (formatting key is A:A on sheet1
If VarType(Target) > vbArray Then Exit Sub
' if multiple cells are changed at once, then exit, as i'm not going to fight with multi cell change

Dim TargetRange As Range
Dim lCell As Object

Set TargetRange = Sh.Range("D1:D9")
' changing cells in this area

For Each lCell In TargetRange.Cells
    If lCell.Value = Target.Value Then
    ' only change cells that match the edited cell
        lCell.Font.Color = Target.Font.Color
        lCell.Interior.Color = Target.Interior.Color
        ' copy whatever you feel needs to be copied
    End If
Next

End Sub

Excel – Microsoft mail merge incorrectly formatting numbers

ROUND works for me. Please make sure you used the correct column in Word. If it still doesn't work in your office version, use the TEXT function as follows:

=TEXT(A1,"0.00") (assuming the numbers are in the first column and you're inserting the formula in row 1)
copy the formula to all rows
make sure that Word uses the column with the correct formula

Best Answer

Related Solutions

Excel: conditionally format a cell using the format of another, content-matching cell

Excel – Microsoft mail merge incorrectly formatting numbers

Related Question