Enter Excel Data Without Decimals

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. Very mysterious, and frustrating for the user.

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.

This won’t be of help to anyone who’s entering a small amount of data, but if you’re entering hundreds of sales orders, where everything has two decimal places, it could save you hundreds of keystrokes.

AmtDecimalEnter

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.

To change the setting in Excel 2007:

  • Click the Office button, then click Excel Options
  • Click the Advanced category
  • In the Editing options section, remove the check mark from ‘Automatically insert a decimal point’, then click OK.

DecPointClear

To change the setting in Excel 2003 or earlier:

On the Tools menu, click Options

On the Edit tab, remove the check mark from Fixed decimal, then click OK.

DecPointClear2003

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *