Excel decimal format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?
 
mlou said:
from the tools menu, i clicked options, edit and "fixed decimal" to two.
However, I don't want this to affect the whole worksheet, just one column.
is there a way to do that?
Click to expand...

Just highlight the column you want it to affect, then right click and
select format cells.

Bill
 
what I really need is to be able to type in 12345 and have it show as 123.45
(without typing the decimal)in just one column, not the whole worksheet. by
right-clicking on the column and formatting cells, I still have to type
decimal, don't I?
 
mlou

For just one column you would need event code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
'remove the $ sign if don't want currency format
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.


Gord Dibben Excel MVP
 
mlou said:
what I really need is to be able to type in 12345 and have it show as 123.45
(without typing the decimal)in just one column, not the whole worksheet. by
right-clicking on the column and formatting cells, I still have to type
decimal, don't I?
Click to expand...


Having a cell display a different number than you've entered into the
cell is beyond anything I can think of. If it were me, I'd type 12345
into one column and have it display in another one. Divide by 100 and
have the other column formatted to display two decimal positions.

Good luck with it...

Bill
 
You could create the custom number format 0\.00
That would easily display 12345 as 123.45, but then if you need to reference
the value in a formula you will have to divide it by 100 in the formula.
 
This custom number format really helped me. Now, is there a way to add the
comma dividing the thousands? #,###.##
 
Assuming you will never have a formatted amount of one million dollars or
more, try this Custom Format...

[<100000]0\.00;0\,000\.00
 
Back
Top