Putting the Decimal Places at Your Service
Remember to use the Fixed Decimal setting when you need to do heavy-duty numerical data entry in a spreadsheet. When you turn on this setting, Excel automatically enters a decimal point using the number of decimal spaces you specify for numeric entries. This speeds up your numeric data entry enormously (especially when you're doing the data entry from the keyboard's numeric keypad) because you no longer have to take time to type the period for the decimal point (you just type digits and press Enter).
To toggle on the Fixed Decimal setting, follow these steps:
- Choose Tools → Options to open the Options dialog box.
- On the Edit tab, select the Fixed Decimal check box.
The moment you activate the Fixed Decimal check box, the Places text box below and to its right becomes available. - Specify the number of decimal places that you want Excel to fix in all the numbers you enter.
By default, the Places text box selects 2 as the number of decimal places to fix in your numbers (which makes sense given that much numeric data entry represents financial figures in dollars and cents). To modify the number of decimal places, enter a new value in the Places text box (between -300 and 300) or use its spinner buttons to select it. - Click OK to close the Options dialog box.
Enter a negative number in the Places text box to have Excel pad the digits you type with trailing zeros equal to this number's absolute value. For example, enter -2 in the Places text box to have all your entries automatically increased by a factor of 100. (For example, if you type 789, Excel enters 78900 in the cell.)
Excel lets you know when the Fixed Decimal setting is turned on by displaying FIX on the far right of the Status bar. While this setting is active, the only time you have to be concerned with decimal points is when the numbers you're entering don't conform to the number of decimal places you set. For example, if you use the two-decimal-place default for entering financial figures in the worksheet, you enter all standard dollar-and-cents numbers simply by typing their digits. (For example, you enter the value $150.24 into a cell by typing the digits 15024 and then pressing Enter - don't you dare take time to type a dollar sign.)
When, however, you come upon a whole dollar amount that has zero cents and therefore doesn't really require any decimal places (they're purely optional), you have to make a choice. Either you can tell Excel where to put the decimal place or end up entering extra trailing zeros. Suppose that you need to enter the value $200.00 and the Fixed Decimal setting with the default two decimal places in turned on. If you type 200 in the current cell, Excel reduces it to 2 when you press Enter. To get the program to put 200 in the cell, either you have to type 200 and then press the period (.) key before Enter, or you have to type 20000 (which looks nothing like the value $200.00).
Don't forget to turn off the Fixed Decimal setting (by clearing its check box on the Edit tab of the Options dialog box) as soon as you finish with your numeric data entry marathon. Otherwise, you or a coworker may be completely thrown off when Excel stubbornly refuses to enter your numbers as you type them.
