Format Cells for Numbers

[meighkee]

Greetings,

I'm looking for a way to format a cell to automatically place the decimal point two places to the right when I type in - say a 3 digit number, and not add two zero's at the end.
I've formatted the cell to use numbers, and 2 decimal places, but it currently places the decimal point at the end and adds two zeros.

Example: I type in the number 350. It adds two zeros and displays 350.00. What I would like is to display 3.50 preventing me from having to manually add the decimal point.

Hope that made sense.

 

[Arcticwarrio]

do in Tools>Options the edit tab and fixed decimal 2 places

 

[Richard Schollar]

In Excel 2003 and below you would do this via Tools>Options>Edit and check Fixed Decimal Places (and ensure set to 2). In 2007/2010 this option will sit in File menu/Office Button>Options/Excel Options>Advanced (I think it's advanced).

 

[Jonmo1]

Click Tools - Options - Edit
Check "Fixed decimal places"
Select 2.

Warning, this will literally change the value from three hundred fifty, to three and one half. This is not just formatting/display.

Then format the cell for 2 decimal places.


Hope that helps.

 

[meighkee]

Works fine

Thanks very much.

 

[meighkee]

I do have a follow up question on this same issue. With regard to automatically placing the decimal point two places to the right by way of going into Excel Options, Advanced tab, etc., which affects Excel globally.
Ideally I would only like to apply it to a specific worksheet/workbook. Not the subsequent spreadsheets I create in the future.

 

[Jonmo1]

You'll need VBA code for that... Is that acceptable?

 

[meighkee]

I'm somewhat familiar with VBA but haven't used it yet. Any help is greatly appreciated.

 

[Arcticwarrio]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True
ErrorHandler:
Application.EnableEvents = True
End Sub