BACKGROUND IMAGE: iSTOCK/GETTY IMAGES
If you've been in the accounting department long enough, you remember the adding machine. One of my favorite features was the sliding switch that allowed you to avoid entering the decimal point. Slide it to the right, and you could simply type "12345" when you wanted the value of $123.45.
Not having to press the decimal point saved a lot of time on the whole, even though you occasionally had an even dollar amount, and had to type "1000" instead of "10" for ten dollars.
Since Microsoft Excel was originally designed for the accounting department, the program offers a setting to emulate this old adding machine feature. However, the Excel equivalent is actually far better than the original feature.
Say that you are tracking property tax levies. A levy with a millage of 7.3 mills is equivalent to a rate of 0.0073.
It would be quite tedious to enter a column of these values. Although you can enter .0073 instead of 0.0073, it will still be fairly slow to enter the decimal points and leading zeroes.
But if you are facing a data entry task where you need to enter many small numbers, you can tell Excel to always move the data entry some number of places to the right of the decimal point.
Go to File, Options, Advanced. Near the top, choose the checkbox for "Automatically Insert a Decimal Point." Change the next Places spin button to 4.
After changing this setting, you can type 200, as shown in Figure 3:
Press Enter, and the value will change to 0.0200.
For 11.1 mills, type 111 as shown above, and the entry will change to 11.1:
Typing 52 results in an entry of 0.0052:
The key to speeding the data entry is to choose the correct value for the Places spin button.
Note: You are allowed to have a negative number in the Places spin button. If you use -3 as the Places value, typing 1234 will become 1,234,000.
About the author:
Bill Jelen, AKA "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.