TIP: How to conditional format number of decimal places

[cbrf23]

I was trying to figure out how to conditionally format how many decimal places to display, and I couldnt find an answer. I decided since I couldn't find an anwer online, I should share what I figured out.

I wanted at least 3 decimal places displayed always. But,if I typed more, I wanted more (up to 6).
Like so:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 width=64>INPUT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=64>DISPLAY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1.000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>10.000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1">.1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.1">.100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123">.123</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.123">.123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1234">.1234</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.1234">.1234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.12345">.12345</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.12345">.12345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123456">.123456</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.123456">.123456</TD></TR></TBODY></TABLE>
To get this to work, you need to set the cell format as custom. And enter: .000###
This works like:

.000 '<--minimum decimal places
### '<--optional decimal places to display

You can get creative too.
For example:
[<1].0000######;#,###
works like:

    [<1] '<--If cell value <1 Then:
        .0000 '<--minimum decimal places
        ###### '<--optional decimal places to display
    ; '<--Else:
        #,### '<--no decimal places/ 0000, displayed as 0,000/ all digits optional

And gives you:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=64>INPUT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 width=64>ALT DISP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1">.1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.1">.1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123">.123</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.123">.1230</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1234">.1234</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.1234">.1234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.12345">.12345</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.12345">.12345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123456">.123456</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.123456">.123456</TD></TR></TBODY></TABLE>

I found this really useful, and thought I would share since I had trouble finding an answer when I was searching.

 

[mikerickson]

Does Conditional Formatting in Windows allow the number format to be conditionally determined? On my Mac, only color and borders and such are avaliable.

 

[cbrf23]

That would work in 2010, but in 2003 number isn't an option in conditional format.

My original goal was to not use conditional formatting, because it makes the file size a lot bigger....

You could format your cells to #,##0.00 and then use VBA to check if the cell value is an integer or not and change formats...

Paste this into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then '<--change to _
      the cells you want to monitor. I have column "B" being monitored... _
      for example if your data is in A1 to B20, change to "A1:B20"
        With Target
            If .Value = Int(.Value) Then .NumberFormat = "#,###"
        End With
    End If
End Sub

 

[cbrf23]

To clarify, after reading back a few posts, my original solution had nothing to do with loosing the period if the number was an integer, it had to do with displaying a minimum number of decimal places, while maintaining the flexibility to display more decimal places (up to whatever maximum you define) if needed.

That was the problem I solved for myself, and that I wanted to share the solution to.

With that being said, you do raise a good point about being able to remove the decimal in an integer. I dont know why, but custom text formatting only allows you to check <,=,>,<=,>=, or <>. So I think the only solution to the problem of the period being left in case of an integer is to use vba.

The code I posted above works for what you are trying to do.

 

[bobaol]

hello, i am trying the code above. i saved the Excel2003 file as format1.xls, i copied the code and pasted it to Module1. But when I press the run macro button, it does not see the macro "Private Sub Worksheet_Change". Maybe I am doing something wrong. I do not know what Private Sub means. I usually record the code like this:
Sub Macro2()
Application.Goto Reference:="R1C1"
Columns("A:M").Select
Selection.NumberFormat = "_(* #,###.##_);[Red]_* - #,###.##;_(* """"??_);_(@_)"
End Sub
I would like to try your code. please help. thanks in advance.

 

[cbrf23]

You need to paste that in the module for the worksheet you want it to work on.