Recently I helped someone who was having trouble entering data in a new installation of Excel 2007.
- When he entered 100, the value was automatically changed to 1.
- If he typed 1000, the value immediately became 10.
He found this very mysterious, and frustrating, as you can imagine.
It’s a Feature
What the user had discovered was a relatively unknown feature of Excel.
If you’re entering a long list of decimal amounts, you can turn on this feature, and the decimal points will be added automatically.
For example, if you’re entering hundreds of sales orders, where everything has two decimal places, it could save you hundreds of keystrokes.
However, this feature won’t be of help to anyone who’s entering a small amount of data. In fact, it will be the opposite of helpful!
How to Change the Setting
I have no idea how this setting would have been turned on in Excel 2007, without the user knowing.
It’s certainly not a default setting when you take Excel out of the box.
Perhaps it was changed accidentally, or by a co-worker who has a strange sense of humour.
Change Setting in Excel 2007
To turn on the automatic decimal point setting in Excel 2007, follow these steps:
- At the top left of the Excel window, click the Office button
- Next, click Excel Options
- At the left, click the Advanced category
- Next, in the Editing options section, remove the check mark from ‘Automatically insert a decimal point’
- Finally, click OK.
Change Setting in Excel 2003 or earlier:
To turn on the automatic decimal point setting in Excel 2003, or earlier versions, follow these steps:
- On the Tools menu, click Options
- On the Edit tab, remove the check mark from Fixed decimal
- Finally, click the OK button, to close the Options window
