Excel number format to only show decimals when necessary

Question

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.

Kv

Maybe something along the lines of if, round, len? You should define your requirements for all situations, for you and the question. – Burkholder

that should be a Microsoft Excel bug... and reported as such, the "#.#" (.NET) format displays "7" as "7" when using "#.#" – Simply

Answer 1

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:

Reposit

Good thinking. If it's not possible to do it with #.# style formatting I'll mark this as correct. I may just record a macro! – Kv

This is an excellent solution, but it does not work if the underlying data is formatted as percents. Because the underlying values are stored as decimals, using MOD() does not produce the desired result. In this case, I used =TRUNC(A1*100)=A1*100. – Pastel

Good answer. As an alternate, could have also used $A1=INT($A1) instead of MOD. Slightly simpler. – Mcilroy

Answer 2

=IF(MOD(A1,1)=0,TEXT(A1,"0"),TEXT(A1,"0.00"))

Sippet

This kind of works, but it unnecessarily creates the need for an extra column just to get the desired number format. It also has the perhaps undesirable effect of causing the cell's contents to be left-aligned, the default for text, instead of right-aligned, the default behavior for numbers. If one is OCD like me, this solution will still force one to change a format for each of these cells. So this really just trades changing a number format manually to changing a horizontal alignment format manually thus saving the user no time and adding an unnecessary extra column. – Flimflam

Answer 3

A bit late to the Thread but I have a report which was calculating percentages to 7 decimal places, but the column also contains integers.

My conclusion was to use conditional formatting so if the Cell Contains a period/decimal point "." then to change the formatting to Number with 2 decimal places. This way if there isn't a period/decimal point then the values remain unedited.

Fairchild

Thanks for showing your idea. This does not work, however, if anyone opens it in French where the comma (,) is used instead of period (.) for decimal place. I tried it and in English it's fine but the formatting behaviour changes if you have another language for your OS. – Colophon

And it doesn’t work if you want to apply a percent format because the percent format from conditional formatting will always apply. – Dashed

Answer 4

If you would like to use the value as text you can use this (using A1 as the number):

=TEXT(A1,IF(MOD(A1,1)=0,"#","#.##"))

Explanation:
IF(MOD(A1,1)=0: This part checks if the number is a whole number with the modulo function.

"#","#.##": Use "#" if the condition is true and "#.##" if the condition is false. These are appropriate format options for integer and decimal numbers accordingly.

The number of hashes after the decimal in "#.##" define the maximum precision, but it will only display as many as required e.g. 2.1 would not be "2.10", but "2.1" instead.

Alternatively: "#.00" can be used to always pad with 0's, which would make "1.3" become "1.30".

Abubekr

Answer 5

Maybe an example used to put coordinates to a point could help.

Sub PutCoord(PtN&, Px#, Py#, S4$)

Px = Round(Px, 2): Py = Round(Py, 2)

 Dim XS$: If Px = Int(Px) Then XS = Format(Px, "0") Else XS = Format(Px, "0.##")

 Dim YS$: If Py = Int(Py) Then YS = Format(Py, "0") Else YS = Format(Py, "0.##")

   Dim WS$: WS = "Pt " & PtN & " @ " & XS & " , " & YS

   With ActiveSheet.Shapes.AddShape(msoShapeDoubleBracket, Px, Py, 90, 20)

      With .TextFrame

         .MarginLeft = 0

         .MarginRight = 0

         .MarginTop = 0

         .MarginBottom = 0

         .Characters.Text = WS

         .AutoSize = msoAutoSizeShapeToFitText

      End With

      .Name = S4 & PtN

   End With

End Sub

Ezechiel

Answer 6

On the Home tab, under the "Number" group, select General from the drop-down menu (it's a built-in number format).
However, unfortunately, if you have numbers with more than 2 decimal places you would have to use something like =ROUND(A1,2) before.

Side note: General can also be used as custom number format code (note that the number format code is language specific and depends on Excels default language, e.g. in French it would be Standard). And General can be modified, for example, General \% adds " %" after the number like so: 5.55 displays as 5.55 % or 1 as 1 %.)

Thigmotropism

Answer 7

Use IF and TEXT formula to format your values.
If your values are in column A, use this formula:

=IF(LEN(A1)=1,TEXT(A1,"0"),TEXT(A1,"0.00"))

This formula basically says:
If length of cell value is 1, then return formatted value as "0", otherwise return value as "0.00".

Mahla

I simplified my question. I want to show 12 as 12 and 123 as 123. – Kv

And unfortunately I don't want to use formulas as I need to set the format of the whole sheet. – Kv