Excel: I type 152 and get 1.52

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: In my copy of Excel, I type 152 and Excel enters 1.52. What is going on?

Strategy: That sounds like a great problem to have? How did you do it?

There is a setting in Excel options to automatically insert a decimal point and a number of decimal places when you type a number. To access it, go to File, Options. Choose the Advanced category. The second setting in Advanced is Automatically Insert a Decimal Point. Choose that setting, and then select the number of decimal places.

  1. This setting affects all worksheets.

    Now, when you enter any number, Excel will divide it by 100.

    Not having to type the decimal point can speed data entry of dollars and cents.

    Gotcha: This setting does not just affect the current worksheet. It is global to all workbooks that you open on this computer. If you had some data entry to do, you could turn on the setting, do the data entry, then turn the setting back off.