Page 95 - Excel Timesaving Techniques for Dummies

17_574272 ch15.qxd 10/1/04 10:29 PM Page 80
80
Technique 15: All Aboard the Numerical Entry Express!
financial figures in the worksheet, you enter all stan- To ensure numerical data entry, you need to use
dard dollar-and-cents numbers simply by typing Excel’s ISNUMBER function in a formula as part of a
their digits. (For example, you enter the value Custom type of data validation setting. ISNUMBER is
$150.24 into a cell by typing the digits 15024 and an Information-type function that returns a logical
then pressing Enter — don’t you dare take time to TRUE value when its argument is numerical and a
type a dollar sign.) logical FALSE value when it’s not numerical. This
function uses the following syntax:
When, however, you come upon a whole dollar
amount that has zero cents and therefore doesn’t =ISNUMBER(value)
really require any decimal places (they’re purely
optional), you have to make a choice. Either you can Whenever the ISNUMBER function returns a TRUE
tell Excel where to put the decimal place or end up value as a part of a Custom-type data validation set-
entering extra trailing zeros. Suppose that you need ting, Excel finds the data entry to be valid and allows
to enter the value $200.00 and the Fixed Decimal set- the data entry. However, whenever the function
ting with the default two decimal places in turned returns a FALSE value, the program finds the entry to
on. If you type 200 in the current cell, Excel reduces be invalid and rejects the data entry.
it to 2 when you press Enter. To get the program to
Figures 15-2 through 15-4 illustrate how you set up
put 200 in the cell, either you have to type 200 and
this type of numerical data validation for a cell range
then press the period (.) key before Enter, or you
in your spreadsheet. For this example, I want to
have to type 20000 (which looks nothing like the
inoculate the cell range C4:E9 with this type of pro-
value $200.00).
tection. This range contains all the numerical values
in my little sales table.
Don’t forget to turn off the Fixed Decimal set-
ting (by clearing its check box on the Edit tab
I start this process by selecting cell C4, the first cell
of the Options dialog box) as soon as you fin-
ish with your numeric data entry marathon. of the range to be protected. It’s here that I define
the numerical data validation that I then copy with
Otherwise, you or a coworker may be com-
pletely thrown off when Excel stubbornly Paste Special to the rest of the range:
refuses to enter your numbers as you type
them! 1. Choose Data➪Validation to open the Data
Validation dialog box.
2. On the Settings tab, select Custom in the Allow
Number Please! drop-down list.
3. In the Formula text box, type =ISNUMBER (and
When it comes to numerical data entry, efficiency is then click cell C4 to enter it as the argument
key — but not if it comes at the expense of accuracy. before typing a close parenthesis [)] to close off
This procedure covers a way that you can use the the function. (See Figure 15-2.)
data validation feature (see Technique 14 for details)
For this data validation setting, you don’t need to
to make it impossible for users to enter anything but
add an input message. (Here, you already know
numerical entries in a cell range. The downside of
what kind of entry you intend to make.) All you
adding this type of data entry safeguard is that it can
need to add is a custom error alert message that
possibly stop the flow of your data entry cold. The
helps the user identify the nature of his or her
upside is that you don’t have to worry about errant
error.
punctuation and letter keystrokes converting cell
entries into text that later spawns error values in for- 4. Select the Error Alert tab.
mulas that reference those cells.