Page 87 - Excel Timesaving Techniques for Dummies

16_574272 ch14.qxd 10/1/04 10:39 PM Page 72
14 Ensuring Accurate
Data Entries with

Technique Data Validation

his technique acquaints you with a very versatile and, in the long
Save Time By run, great timesaving feature that Excel calls data validation. You
Tuse this nifty feature to prevent the users of your spreadsheets
Setting up data validation
criteria from ever getting a chance to enter the wrong type of data entry or a
type that’s not on your list (when dealing with labels) or within a tolera-
Creating input messages ble range (when dealing with numbers) in critical cells of its worksheets.
Using error alerts
Not only can you restrict the types of data entries that you or any other
spreadsheet user can make, you can also add input messages to your val-
idated cells that indicate what types of entries are permissible there. As
if this weren’t enough, you can also tag the validated cells with error
alerts that flash an alert symbol along with a custom message indicating
the data entry rules for the current cell!

Only the Valid Need Apply

The heart and soul of the data validation feature is the Settings tab of the
Data Validation dialog box (Data➪Validation). The Settings tab, shown in
Figure 14-1, is where you specify the criteria under which Excel considers
any data entry made in the current cell to be kosher.

The first control on the Settings tab is the Allow drop-down list box. By
default, this box is set to Any Value to allow you to do any type of data
entry in the current cell. To change all that anything-goes stuff by making
only a certain type of entry permissible, select one of the other Allow
options:

Whole Number restricts data entry to a whole number that is equal to,
greater than, less than, or within a range of numbers that you then
specify.
Decimal restricts data entry to a decimal number that is equal to,
greater than, less than, or within a range of values that you then
specify.