BACKGROUND IMAGE: iSTOCK/GETTY IMAGES

This content is part of the Essential Guide: Ultimate Excel finance guide
Q
Manage Learn to apply best practices and optimize your operations.

Excel tip: Entering numbers in thousandths

Microsoft Excel consultant Bill Jelen, AKA MrExcel, demonstrates an adding-machine-inspired Excel trick.

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.

Rate column
Figure 1

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.

advanced Excel options
Figure 2

After changing this setting, you can type 200, as shown in Figure 3:

rate column
Figure 3

Press Enter, and the value will change to 0.0200.

rate column
Figure 4

For 11.1 mills, type 111 as shown above, and the entry will change to 11.1:

Rate column
Figure 5

Typing 52 results in an entry of 0.0052:

rate column
Figure 6

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.

You will be able to add details on the next page.

Join the conversation

3 comments

Send me notifications when other members comment.

Please create a username to comment.

Very useful tip! Will use it often and tell my students about this tip.
Cancel
A good tip but the option is global, so will have to turn it on and off. Another solution would be to have an adjacent column with a simple formula that changes decimal point according to a parameter you can specify per column.
Cancel
Another useful tip. Just wish it was worksheet specific instead of global.
Cancel

-ADS BY GOOGLE

SearchOracle

SearchDataManagement

SearchSAP

SearchBusinessAnalytics

SearchSQLServer

SearchContentManagement

SearchHRSoftware

Close